How do I use T-SQL Group By
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
SELECT count(userID), userName FROM users GROUP BY userName
When else would
GROUP BY be useful, and what are the performance ramifications?
5 Solutions collect form web for “How do I use T-SQL Group By”
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.
GROUP BY is similar to DISTINCT in that it groups multiple records into one.
This example, borrowed from http://www.devguru.com/technologies/t-sql/7080.asp, lists distinct products in the Products table.
SELECT Product FROM Products GROUP BY Product Product ------------- Desktop Laptop Mouse Network Card Hard Drive Software Book Accessory
The advantage of GROUP BY over DISTINCT, is that it can give you granular control when used with a HAVING clause.
SELECT Product, count(Product) as ProdCnt FROM Products GROUP BY Product HAVING count(Product) > 2 Product ProdCnt -------------------- Desktop 10 Laptop 5 Mouse 3 Network Card 9 Software 6
Group By forces the entire set to be populated before records are returned (since it is an implicit sort).
For that reason (and many others), never use a Group By in a subquery.
GROUP BY also helps when you want to generate a report that will average or sum a bunch of data. You can GROUP By the Department ID and the SUM all the sales revenue or AVG the count of sales for each month.
Counting the number of times tags are used might be a google example:
SELECT TagName, Count(*) AS TimesUsed FROM Tags GROUP BY TagName ORDER TimesUsed
If you simply want a distinct value of tags, I would prefer to use the
SELECT DISTINCT TagName FROM Tags ORDER BY TagName ASC