sql server - How do I use T-SQL Group By


Translate

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?


Toutes les réponses
  • Translate

    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.


  • Translate

    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
    

  • Translate

    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.


  • Translate

    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
    

  • Translate

    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.