Find a solution

sql server - Jak mohu použít T

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

Vím, že musím mít (i když nevím proč) klauzuli GROUP BY na konci dotazu SQL, který používá jakékoli agregované funkce, jako je počet, součet, průměr, atd.: VÝBĚR počet (userID), userName OD uživatele. ..

Toto je shrnutí po překladu. Pokud potřebujete zobrazit celý překlad, klikněte na ikonu „přeložit“

Všechny odpovědi
  • Chris Farmer
    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.

  • Seibar
    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
  • Stu
    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.

  • GateKiller
    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
  • Dillie-O
    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.

Podobné problémy

Poslední čísla