EMZETT.Aggregatfunktionen
Aggregatfunktionen fassen viele Zeilen zu einem Wert zusammen:
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
(1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
(4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
(7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT count(*) AS anzahl, count(punkte) AS mit_punkten, sum(punkte) AS summe,
avg(punkte) AS durchschnitt, min(punkte) AS minimum, max(punkte) AS maximum
FROM kunden;anzahl mit_punkten summe durchschnitt minimum maximum ------ ----------- ----- ------------ ------- ------- 5 4 550 137.5 80 200
count(*)zählt Zeilen,count(spalte)nur Zeilen mit Wert (nicht NULL)sum,avg,min,maxignorieren NULLcount(DISTINCT spalte)zählt verschiedene Werte
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
(1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
(4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
(7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT count(DISTINCT produkt) AS verschiedene, count(*) AS positionen,
sum(preis * menge) AS umsatz, round(avg(preis), 2) AS schnittspreis
FROM bestellungen;verschiedene positionen umsatz schnittspreis ------------ ---------- ------ ------------- 5 7 2328.4 327.56
GROUP BY: Gruppen bilden
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
(1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
(4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
(7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT ort, count(*) AS anzahl, sum(punkte) AS summe
FROM kunden
GROUP BY ort
ORDER BY anzahl DESC, ort;
SELECT produkt, count(*) AS verkauft, sum(menge) AS stueck, sum(preis * menge) AS umsatz
FROM bestellungen
GROUP BY produkt
ORDER BY umsatz DESC;ort anzahl summe ------- ------ ----- Berlin 2 320 Hamburg 1 80 Köln 1 150 München 1 produkt verkauft stueck umsatz -------- -------- ------ ------ Laptop 2 2 1998.0 Monitor 1 1 189.9 Maus 2 3 76.5 Tastatur 1 1 49.0 Kabel 1 3 15.0
Jede Spalte im SELECT ist entweder in GROUP BY oder in einer Aggregatfunktion. (SQLite erlaubt Abweichungen mit überraschendem Ergebnis, PostgreSQL meldet einen Fehler.)
HAVING: Gruppen filtern
WHERE filtert Zeilen vor dem Gruppieren, HAVING filtert Gruppen danach:
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
(1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
(4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
(7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT produkt, sum(menge) AS stueck
FROM bestellungen
WHERE preis < 500
GROUP BY produkt
HAVING sum(menge) >= 2
ORDER BY stueck DESC;produkt stueck ------- ------ Maus 3 Kabel 3
Nach mehreren Spalten gruppieren
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
(1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
(4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
(7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT strftime('%Y-%m', datum) AS monat, produkt, count(*) AS anzahl
FROM bestellungen
GROUP BY monat, produkt
ORDER BY monat, produkt;monat produkt anzahl ------- -------- ------ 2025-01 Laptop 1 2025-01 Maus 1 2025-02 Monitor 1 2025-02 Tastatur 1 2025-03 Kabel 1 2025-03 Laptop 1 2025-03 Maus 1
Gruppen-Summen mit Bedingung
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
(1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
(4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
(7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT
sum(CASE WHEN preis >= 100 THEN 1 ELSE 0 END) AS teuer,
sum(CASE WHEN preis < 100 THEN 1 ELSE 0 END) AS guenstig,
group_concat(DISTINCT produkt) AS produkte
FROM bestellungen;teuer guenstig produkte ----- -------- ---------------------------------- 3 4 Laptop,Maus,Monitor,Tastatur,Kabel
group_concat (PostgreSQL: string_agg, MySQL: GROUP_CONCAT) verbindet Werte zu einem Text.
Merke
- Aggregate:
count,sum,avg,min,max,group_concat GROUP BYbildet Gruppen, jede Spalte imSELECTgehört inGROUP BYoder eine AggregatfunktionWHEREfiltert Zeilen,HAVINGfiltert Gruppencount(*)zählt Zeilen,count(spalte)nur Nicht-NULLCASEinnerhalb vonsumergibt bedingte Zählungen
Aufgabe
Berechne je Kunde-ID Anzahl und Gesamtumsatz der Bestellungen und zeige nur Kunden mit mehr als 100 Umsatz.