Seibar Seibar - 2 months ago 5x
SQL Question

How do I use T-SQL Group By

I know I need to have (although I don't know why) an Order By clause on the end of a SQL query that uses any aggregate functions like count, sum, avg, etc:

select count(userID), userName from users group by userName

When else would GROUP BY be useful, and what are the performance ramifications?


To retrieve the number of widgets from each widget category that has more than 5 widgets, you could do this:

SELECT WidgetCategory, count(*)
FROM Widgets
GROUP BY WidgetCategory
HAVING count(*) > 5

The "having" clause is something people often forget about, instead opting to retrieve all their data to the client and iterating through it there.