Seibar Seibar - 4 months ago 6
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?

Answer

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.

Comments