CODEWITHSUNDEEP
sqlms-accessgroup-by
aofe1337
aofe1337

Reputation: 67

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

Upvotes: 0

Views: 69

Answers (3)

Max
Max

Reputation: 387

In your previous question with slightly different query How to get top 10 and ORDER BY() from COUNT() concerning the same database, I suggested using a different query. (It would be nice if you accepted an answer, even if that answer isn't mine, as long as it answered your question.)

The query I suggested can be easily altered to fit your needs.

SELECT userid, COUNT(tcode) as transactions_count
FROM sap_security_log
GROUP BY userid
ORDER BY transactions_count DESC LIMIT 10
  • You can add tcode to the GROUP BY expression so that you have GROUP BY tcode, userid.
  • You'll probably want to change the ORDER BY expression to something more relevant. I suggest you ORDER BY tcode and then transactions_count
  • No need for LIMIT 10 anymore since you're probably looking for all the results, not just 10.

This would lead to the following code:

SELECT userid, tcode, COUNT(tcode) as transactions_count
FROM sap_security_log
GROUP BY tcode, userid
ORDER BY tcode, transactions_count DESC

Which you can view in the following working example: http://sqlfiddle.com/#!9/016f93/1

Upvotes: 1

user6684540
user6684540

Reputation:

Try : your query should be like:

  Select TCODE, Tcount, Userid, TTEXT
  From Sap_Security_Log
  Group by Tcode,Tcount,Userid,Ttext;

Or if want Group By Tcount :

Select TCODE, Sum(Tcount) As Count, Userid, TTEXT
  From Sap_Security_Log
  Group by Tcode;

Good Luck.

Upvotes: 0

Dennisvdh
Dennisvdh

Reputation: 120

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.

Upvotes: 0

Related Questions