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 1

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 your simply wanting a distinct value of tags, I would prefer to use the distant statement.

Select Distinct TagName From Tags Order By TagName Asc

Answer 2

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.

Answer 3

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.

Answer 4

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