sql server - Come posso usare T

original title: "sql server - 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?

So che devo avere (anche se non so perché) una clausola GROUP BY alla fine di una query SQL che utilizza qualsiasi funzione aggregata come count, sum, avg, ecc: SELECT count (userID), userName FROM user. ..

Questo è il riepilogo dopo la traduzione, se è necessario visualizzare la traduzione completa, fare clic sull'icona "traduci"

Tutte le risposte
  • 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
    Network Card
    Hard Drive

    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.

    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.