sql server - How do I use T-SQL Group By

Question

I know I need to have (although I don't know why) a GROUP 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?

Answer 1

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.