Board index » delphi » Aggregate function, Grouping

Aggregate function, Grouping


2006-05-19 06:32:03 PM
delphi187
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
 
 

Re:Aggregate function, Grouping

Kim,
The 'group by' clause references 'bl.BU_KEY' but that field isn't
included in the 'select' clause.
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" <XXXX@XXXXX.COM>writes
Quote
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
 

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 writes:
Quote
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" <XXXX@XXXXX.COM>writes
news:446d9ea3$XXXX@XXXXX.COM...

>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
 

Re:Aggregate function, Grouping

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

Re:Aggregate function, Grouping

Thanks Vitali, your example works exactly as I'd like.
Cheers!
Vitali Kalinin writes:
Quote
How about this:
SELECT
bl.GR_KEY, bl.GroupOrder, bl.UseOverhead,
SUM (Amount * price) as "SubTotal",
SUM(IIF (bl.UseClientAmount = True,
bl.Amount + bl.ClientAmount,
bl.Amount)) as "RealAmount"
FROM
Budget_Lines bl
WHERE
bl.BU_KEY = :BU_KEY
GROUP BY
bl.BU_KEY, bl.GroupOrder, bl.UseOverhead, bl.GR_KEY