aofe1337 aofe1337 - 3 months ago 8
SQL Question

How to get top 10 and ORDER BY() from COUNT()

The image below is an example of the database I have to work with.

There are more fields to the database - userid goes till USER-0050

What I want to achieve is a top 10 user activity. Based on the image above the desired output is:

User ID Transaction
USER-005 14
USER-001 12
USER-002 12
USER-003 8
USER-004 6


What I have tried so far:

SELECT DISTINCT (userid), count(TCODE) as Transaction
FROM SAP_SECURITY_LOG
GROUP BY TCODE, USERID


However my output isn't what I want to achieve. I get

UserID Transaction
User-001 0
User-001 1
User-001 2
User-001 3
User-001 6


Basically I want to merge all these together and then get the top 10 transactions of all userids (of all 50 userids)

Any guidance is appreciated. I hope what I'm trying to say makes sense
Image 1

My output:

Image 2

Keeps going till user-0050

Answer

Top active users :

SELECT Top(10) userid, count(TCODE) as Transaction
FROM SAP_SECURITY_LOG
GROUP BY userID
ORDER BY count(TCODE) DESC
Comments