How do I use T-SQL Group By *

Question

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.

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/2702/" >How do I use T-SQL Group By< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.