CODEWITHSUNDEEP
sqlsql-server
BijiDabes
BijiDabes

Reputation: 3

how to select other column before aggregate of a subquery

select Name,TotalTrans as asd from (select Name, COUNT(name) as
TotalTrans from trans group by Name) as sub1 group by Name, TotalTrans
having TotalTrans = MAX(TotalTrans)

i want to get the name and the max value of the total transaction(count) that has been occured on the table

Upvotes: 0

Views: 31

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

I would use a TOP query here:

SELECT TOP 1 name, COUNT(name) AS TotalTrans
FROM trans
GROUP BY name
ORDER BY COUNT(name) DESC;

If there could be more than one name tied for the highest count, then use TOP 1 WITH TIES or add another sorting level to ORDER BY which breaks the tie.

Upvotes: 1

Related Questions