Zoeken…


Eenvoudig groeperen

Orderentabel

Klanten ID Product-ID Aantal stuks Prijs
1 2 5 100
1 3 2 200
1 4 1 500
2 1 4 50
3 5 6 700

Bij groepering op een specifieke kolom worden alleen unieke waarden van deze kolom geretourneerd.

SELECT customerId
FROM orders
GROUP BY customerId;

Winstwaarde:

Klanten ID
1
2
3

Geaggregeerde functies zoals count() toepassing op elke groep en niet op de volledige tabel:

SELECT customerId, 
       COUNT(productId) as numberOfProducts,
       sum(price) as totalPrice
FROM orders
GROUP BY customerId;

Winstwaarde:

Klanten ID numberOfProducts totale prijs
1 3 800
2 1 50
3 1 700

GROEPEN op meerdere kolommen

Misschien wil je GROEPEN op meer dan één kolom

declare @temp table(age int, name varchar(15))

insert into @temp
select 18, 'matt' union all
select 21, 'matt' union all
select 21, 'matt' union all
select 18, 'luke' union all
select 18, 'luke' union all
select 21, 'luke' union all
select 18, 'luke' union all
select 21, 'luke'

SELECT Age, Name, count(1) count
FROM @temp 
GROUP BY Age, Name

groepeert op leeftijd en naam en produceert:

Leeftijd Naam tellen
18 luke 3
21 luke 2
18 mat 1
21 mat 2

Groeperen op met meerdere tabellen, meerdere kolommen

Groeperen op wordt vaak gebruikt met join-instructie. Laten we aannemen dat we twee tabellen hebben. De eerste is de tabel met studenten:

ID kaart Voor-en achternaam Leeftijd
1 Matt Jones 20
2 Frank Blue 21
3 Anthony Angel 18

Tweede tabel is de tabel met onderwerpen die elke student kan volgen:

Subject_Id Onderwerpen
1 Wiskunde
2 PE
3 Fysica

En omdat één student veel vakken kan volgen en één vak door veel studenten kan worden bijgewoond (dus N: N-relatie), hebben we een derde "begrenzende" tabel nodig. Laten we de tabel Students_subjects noemen:

Subject_Id student_id
1 1
2 2
2 1
3 2
1 3
1 1

Laten we nu zeggen dat we willen weten hoeveel vakken elke student volgt. Hier is de zelfstandige GROUP BY instructie niet voldoende, omdat de informatie niet beschikbaar is via een enkele tabel. Daarom moeten we GROUP BY met de JOIN verklaring:

Select Students.FullName, COUNT(Subject Id) as SubjectNumber FROM Students_Subjects
LEFT JOIN Students
ON Students_Subjects.Student_id = Students.Id
GROUP BY Students.FullName

Het resultaat van de opgegeven zoekopdracht is als volgt:

Voor-en achternaam SubjectNumber
Matt Jones 3
Frank Blue 2
Anthony Angel 1

Voor een nog ingewikkelder voorbeeld van gebruik van GROUP BY, laten we zeggen dat student mogelijk hetzelfde onderwerp meerdere keren aan zijn naam kan toewijzen (zoals weergegeven in de tabel Students_Subjects). In dit scenario kunnen we mogelijk het aantal keren tellen dat elk onderwerp door GROEPING aan meer dan één kolom is toegewezen aan een student:

SELECT Students.FullName, Subjects.Subject,
COUNT(Students_subjects.Subject_id) AS NumberOfOrders
FROM ((Students_Subjects
INNER JOIN Students
ON Students_Subjcets.Student_id=Students.Id)
INNER JOIN Subjects
ON Students_Subjects.Subject_id=Subjects.Subject_id)
GROUP BY Fullname,Subject

Deze zoekopdracht geeft het volgende resultaat:

Voor-en achternaam Onderwerpen SubjectNumber
Matt Jones Wiskunde 2
Matt Jones PE 1
Frank Blue PE 1
Frank Blue Fysica 1
Anthony Angel Wiskunde 1

HEBBEN

Omdat de WHERE clausule wordt geëvalueerd vóór GROUP BY , kunt u WHERE niet gebruiken om resultaten van de groepering naar beneden te halen (meestal een aggregatiefunctie, zoals COUNT(*) ). Om aan deze behoefte te voldoen, kan de HAVING clausule worden gebruikt.

Bijvoorbeeld met behulp van de volgende gegevens:

DECLARE @orders TABLE(OrderID INT, Name NVARCHAR(100))

INSERT INTO @orders VALUES
( 1, 'Matt' ),
( 2, 'John' ),
( 3, 'Matt' ),
( 4, 'Luke' ),
( 5, 'John' ),
( 6, 'Luke' ),
( 7, 'John' ),
( 8, 'John' ),
( 9, 'Luke' ),
( 10, 'John' ),
( 11, 'Luke' )

Als we het aantal bestellingen willen krijgen dat elke persoon heeft geplaatst, zouden we gebruiken

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name

en krijg

Naam bestellingen
Mat 2
John 5
Luke 4

Als we dit echter willen beperken tot personen die meer dan twee bestellingen hebben geplaatst, kunnen we een HAVING clausule toevoegen.

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name
HAVING COUNT(*) > 2

zal opbrengen

Naam bestellingen
John 5
Luke 4

Merk op dat, net als GROUP BY , de kolommen in HAVING exact moeten overeenkomen met hun tegenhangers in de SELECT instructie. Als we in het bovenstaande voorbeeld hadden gezegd

SELECT Name, COUNT(DISTINCT OrderID)

onze HAVING clausule zou moeten zeggen

HAVING COUNT(DISTINCT OrderID) > 2

GROEPEN DOOR met ROLLUP en CUBE

De operator ROLLUP is handig bij het genereren van rapporten die subtotalen en totalen bevatten.

  • CUBE genereert een resultatenset die aggregaties toont voor alle combinaties van waarden in de geselecteerde kolommen.

  • ROLLUP genereert een resultatenset die aggregaten toont voor een hiërarchie van waarden in de geselecteerde kolommen.

    Item Kleur Aantal stuks
    Tafel Blauw 124
    Tafel Rood 223
    Stoel Blauw 101
    Stoel Rood 210
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00 

(7 rij (en) getroffen)

Als het ROLLUP-trefwoord in de query wordt gewijzigd in CUBE, is de CUBE-resultaatset hetzelfde, behalve dat deze twee extra rijen aan het einde worden geretourneerd:

ALL                  Blue                 225.00                     
ALL                  Red                  433.00 

https://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx



Modified text is an extract of the original Stack Overflow Documentation
Licentie onder CC BY-SA 3.0
Niet aangesloten bij Stack Overflow