Microsoft SQL Server
GROEPEN OP

Eenvoudig groeperen

Orderentabel

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

SELECT customerId
FROM orders
GROUP BY customerId;

Winstwaarde:

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:

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:

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:

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

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:

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

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

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

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