Re: Aggregate function, Grouping

Ok, thanks, I understand.
BUT, the grouping problem I have first occurs when I include the fields
bl.UseClientAmount, bl.Amount, bl.ClientAmount
in the Group By part. I don't want these fields there, can I avoid them?

Michael Jacobs wrote:

Since the 'having' clause selects on the 'bl.bu_key' and it will be a specific value you can either include it in the 'select' clause or remove it from the 'group by'

hth,
Michael

"Kim S" <ks@xxxxxxxxxxxxxxx> wrote in message news:446d9ea3$1@xxxxxxxxxxxxxxxxxxxxxxxxx

When I try to execute the sql statement:

SELECT
bl.GR_KEY, bl.GroupOrder, bl.UseOverhead,
SUM (Amount * price) as "SubTotal",
IIF (bl.UseClientAmount = True,
bl.Amount + bl.ClientAmount,
bl.Amount) as "RealAmount"
FROM
Budget_Lines bl
GROUP BY
bl.BU_KEY, bl.GroupOrder, bl.UseOverhead, bl.GR_KEY
HAVING
bl.BU_KEY = :BU_KEY

I get an exception, because the Group By fields do not match the Select fields.

But if I include the fields:

GROUP BY
bl.BU_KEY, bl.GroupOrder, bl.UseOverhead, bl.GR_KEY,
bl.UseClientAmount, bl.Amount, bl.ClientAmount

- the result is wrongly grouped. Can I avoid this problem?

Regards,
Kim
.