Adding fields produces aggregate function error

[Brett]

Hi all:

I have a SELECT query I'm having problems with (returns the error
message "you tried to execute a query that does not include the
specified expression X as part of an aggregate function").

Here's the SQL (yes, I realize this is a bad database design, but I
got this from a client, so I'm forced to work with it):

SELECT ID_SK,SPECIALTY,ADOPTER_FLAG,TYPE,STATUS,STATE,TERRITORY_ID,
(Nz(NRX_8_11_06,0)+Nz(NRX_8_18_06,0)+Nz(NRX_8_25_06,0)+Nz(NRX_9_01_06,0)+Nz(NRX_9_08_06,0)+Nz(NRX_9_15_06,0)+Nz(NRX_9_22_06,0)+Nz(NRX_9_29_06,0)
+Nz(NRX_10_06_06,0)+Nz(NRX_10_13_06,0)+Nz(NRX_10_20_06,0)+Nz(NRX_10_27_06,0))
AS NRX_PRE,
(Nz(NRX_11_03_06,0)+Nz(NRX_11_10_06,0)+Nz(NRX_11_17_06,0)+Nz(NRX_11_24_06,0)+Nz(NRX_12_1_06,0)+Nz(NRX_12_8_06,0)+Nz(NRX_12_15_06,0)+Nz(NRX_12_22_06,0)
+Nz(NRX_12_29_06,0)+Nz(NRX_1_05_07,0)+Nz(NRX_1_12_07,0)+Nz(NRX_1_19_07,0))
AS NRX_POST,
(Nz(PT_8_11_06,0)+Nz(PT_8_18_06,0)+Nz(PT_8_25_06,0)+Nz(PT_9_01_06,0)+Nz(PT_9_08_06,0)+Nz(PT_9_15_06,0)+Nz(PT_9_22_06,0)+Nz(PT_9_29_06,0)+
Nz(PT_10_06_06,0)+Nz(PT_10_13_06,0)+Nz(PT_10_20_06,0)+Nz(PT_10_27_06,0))
AS CALLS_PRE,
(Nz(PT_11_03_06,0)+Nz(PT_11_10_06,0)+Nz(PT_11_17_06,0)+Nz(PT_11_24_06,0)+Nz(PT_12_01_06,0)+Nz(PT_12_08_06,0)+Nz(PT_12_15_06,0)+Nz(PT_12_22_06,0)+
Nz(PT_12_29_06,0)+Nz(PT_1_05_07,0)+Nz(PT_1_12_07,0)+Nz(PT_1_19_07,0))
AS CALLS_POST
FROM SecondStepResults
WHERE ( (STATUS="Active") AND
SPECIALTY IN ("Cardiologist","Interventional Cardiology","Internal
Medicine","Nurse Practitioner","Card Office - NP","FP/GP") AND
STATE IN
("NY","PA","NJ","CT","MA","RI","GA","SC","FL","TN","IN","KY","OH","MI","WI","IL","LA","MS","AL","TX","AR","KS","MO","NE","IA","CA","OR","HI","AZ","NV","UT","ID","WA","NM","WV","VA","NC","MC","DC"))
GROUP BY ID_SK,SPECIALTY,ADOPTER_FLAG,TYPE,STATUS,STATE,TERRITORY_ID
HAVING (NRX_PRE<3 AND NRX_POST<5 AND CALLS_PRE<12 AND CALLS_POST<12);

This is failing on the sum across fields expressions (Nz(...), i.e.,
in the error message mentioned above, X is the expression summing
across fields). I was under the impression that only the non-function
fields needed to be included in the Group By clause.

I've also tried creating an intermediate query without the WHERE and
HAVING clauses and get the same error, so it is something about the
summing across field statement that it doesn't like.

Any idea what is wrong in the above SQL?

Thanks,
Brett

 

[Michel Walsh]

Every field involved in the SELECT clause, of a GROUP BY query, must either
be aggregated, either in the GROUP BY clause. That also holds for any field
involved in the HAVING clause. On the other hand, that is NOT necessary in
the WHERE clause.


So, first, why do you use a GROUP BY (total) query since you don't use any
aggregate (MIN, MAX, SUM, COUNT, AVeraGe, FIRST, LAST, ... ) ? IF no need,
remove the GROUP BY clause, move the HAVING criteria into the WHERE clause
and that would be it.

***if*** you need a GROUP BY clause, all the fields in the HAVING clause
seem to belong to the WHERE clause, rather than into the HAVING clause. Move
the criteria.


WHERE
( (STATUS="Active")
AND
SPECIALTY IN ("Cardiologist","Interventional Cardiology","Internal
Medicine","Nurse Practitioner","Card Office - NP","FP/GP")
AND STATE IN (...)
AND (NRX_PRE<3 AND NRX_POST<5 AND CALLS_PRE<12 AND CALLS_POST<12)

GROUP BY
ID_SK,SPECIALTY,ADOPTER_FLAG,TYPE,STATUS,STATE,TERRITORY_ID;


But that is not all, you must also decide what king of aggregate you need
for your computed expression, in the SELECT clause. Maybe a SUM ?




SELECT ID_SK,SPECIALTY,ADOPTER_FLAG,TYPE,STATUS,STATE,TERRITORY_ID,
SUM (
(Nz(NRX_8_11_06,0)+Nz(NRX_8_18_06,0)+Nz(NRX_8_25_06,0)+Nz(NRX_9_01_06,0)+
Nz(NRX_9_08_06,0)+Nz(NRX_9_15_06,0)+Nz(NRX_9_22_06,0)+Nz(NRX_9_29_06,0)+
Nz(NRX_10_06_06,0)+Nz(NRX_10_13_06,0)+Nz(NRX_10_20_06,0)+Nz(NRX_10_27_06,0))
) AS NRX_PRE,

SUM( ... ) NRX_POST,
...



Again, the first:
ID_SK,SPECIALTY,ADOPTER_FLAG,TYPE,STATUS,STATE,TERRITORY_ID
also appear in the GROUP BY clause, so they don't need aggregate, but other
expressions were not in the GROUP BY, so NEED an aggregate of some kind. *I*
cannot know which one is the right one, and I suspect you don't need a total
query, in the first place (so, just removing the GROUP BY clause would be
fine).


Hoping it may help,
Vanderghast, Access MVP

 

[Duane Hookom]

Your expressions/fields must be included in the group by if they aren't an
aggregate across records. Your expressions calculate across fields (which
apparently you understand is not good practice).

 

[Brett]

Again, the first:
ID_SK,SPECIALTY,ADOPTER_FLAG,TYPE,STATUS,STATE,TERRITORY_ID
also appear in the GROUP BY clause, so they don't need aggregate, but other
expressions were not in the GROUP BY, so NEED an aggregate of some kind. *I*
cannot know which one is the right one, and I suspect you don't need a total
query, in the first place (so, just removing the GROUP BY clause would be
fine).

Access insisted that a GROUP BY be present, so I wasn't getting around
that.

However, I managed to answer my own question (same answer as Duane
provided): I put the entire Nz(...) set of expressions in the GROUP BY
clause and the query ran. Finally ended up just setting up a SELECT
query to produce a table with these calculated columns, then ran
another query on that to select out the records I was after (folding
in the WHERE and HAVING clauses above).

Anyway, thanks for the help, and time to "educate" the client as to
what a normalized database is and why that is a good thing in
situations such as this...

Brett