how to select other column before aggregate of a subquery

November 14, 2021

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

>Solution :

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.