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" <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


.

Relevant Pages

  • Re: Where clause is not working
    ... Your 2nd example has the quote mark in the wrong place. ... Since the WHERE clause evaluates to True, ... Avoid using Name as a field name in Access. ... Consider renaming the field to something else and see if it works. ...
    (microsoft.public.access.queries)
  • Re: When is finally justified?
    ... doesn't finally simply help you avoid ... a finally clause has no reason to exist if there ... it helps you avoid duplicating code." ... or open database connections. ...
    (comp.lang.java.programmer)
  • Re: Replacement for Resume Next
    ... > I'm using VS 2005 now and would like to know if .Net has a replacement for ... > the Resume Next clause. ... > GetDirectories throws an exception. ... do you have a reason to avoid the use ...
    (microsoft.public.dotnet.languages.vb)
  • Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpola
    ... And I've tended to avoid ... SQL 3VL, by forumlating my queries carefully. ... clause on ALL processes using a specific table. ...
    (comp.databases.theory)
  • Re: Netiquette in this newsgroup
    ... clause), no, I got that right ... I usually try to avoid discussions about netiquette; ...
    (microsoft.public.dotnet.languages.vb)