aofe1337 aofe1337 - 4 months ago 9
SQL Question

Error when GROUP BY parameters are changed

I'm having troubles with

GROUP BY
in MS Access.

Question:
When I remove "tcount,userid, ttext" off the
GROUP BY
line I get an error: "Your query does not include the specified expression "tcount" as part of an aggregate function"

The query:

Select TCODE, Tcount, Userid, TTEXT
From Sap_Security_Log
Group by Tcode,tcount, userid, ttext


The results:
enter image description here

What I want:
GROUP BY TCODE
, example result set:

TCODE TCOUNT USERID TTEXT
SESS 13 USER-001
SESS 8 USER-003
SESS 1 USER-002
... ... ...

Answer

You are now selecting all the columns instead of counting the values you want. Try the following query

Select TCODE, Sum(Tcount), Userid, TTEXT
From Sap_Security_Log
Group by Tcode, userid, ttext

This sums up the Tcount for all the rows in which all the values in the columns in your 'group by' are the same. Note that the column Tcount is not in the group by.