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 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?

  • How to use ROW_NUMBER() in UPDATE clause?
  • SQL not delivering expected result with RIGHT JOIN
  • How to check if cursor exists (open status)
  • ntext record won't display if I display a datetime recordset ahead of it
  • PHP PDO_mssql SQLSTATE Adaptive Server connection failed (severity 9)
  • Cannot create SQL Server 2000 Linked Server, Linked to Self
  • 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 DISTINCT statement.

    SELECT DISTINCT TagName
    FROM Tags
    ORDER BY TagName ASC
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.