Aggregate mit Gruppierung in SQL (MySQL )
- Aussprachehinweis
- duplicate (n) ˈduːplɪkət
»GROUP BY« Nach FROM-Klausel, um Spaltenstatiken für jede Gruppe zu erhalten.
<table expression> ::=
<from clause>
[ <where clause> ]
[ <group by clause> ]
Durch eine GROUP-BY-Klausel werden alle Zeilen mit gleichem Wert zusammengefaßt.
Ein Spaltenname, der in der Auswahlliste verwendet wird, muß also entweder Argument einer Statistikfunktion sein oder in der GROUP-BY-Klausel vorkommen.
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE VORNAME
( GESCHLECHT CHAR( 1 ),
VORNAME VARCHAR( 255 ) )
COMMENT = "Der Vorname VORNAME hat das Geschlecht GESCHLECHT.";INSERT INTO VORNAME ( GESCHLECHT, VORNAME )
VALUES ( 'w', 'Mia' ),
( 'w', 'Hannah' ),
( 'm', 'Ben' ),
( 'w', 'Emma' ),
( 'm', 'Luca' ),
( 'm', 'Paul' );SELECT GESCHLECHT, VORNAME FROM VORNAME;
+------------+---------+
| GESCHLECHT | VORNAME |
+------------+---------+
| w | Mia |
| w | Hannah |
| m | Ben |
| w | Emma |
| m | Luca |
| m | Paul |
+------------+---------+SELECT GESCHLECHT, GROUP_CONCAT( VORNAME SEPARATOR ', ' ) AS VORNAMEN FROM VORNAME GROUP BY GESCHLECHT;
+------------+-------------------+
| GESCHLECHT | VORNAMEN |
+------------+-------------------+
| m | Ben, Luca, Paul |
| w | Mia, Hannah, Emma |
+------------+-------------------+SELECT GESCHLECHT, GROUP_CONCAT( VORNAME ORDER BY VORNAME SEPARATOR ', ' ) AS VORNAMEN FROM VORNAME GROUP BY GESCHLECHT;
+------------+-------------------+
| GESCHLECHT | VORNAMEN |
+------------+-------------------+
| m | Ben, Luca, Paul |
| w | Emma, Hannah, Mia |
+------------+-------------------+
Weitere Beispiele
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE T
( C INT COMMENT 'Beispielspalte ohne Sinn',
C1 INT COMMENT 'Beispielspalte ohne Sinn' )
COMMENT = "sinnlose Beispieltabelle";INSERT INTO T ( C, C1 )
VALUES ( 1, 1 ), ( 1, 1 ), ( 1, 1 ), ( 1, 1 );SELECT * FROM T;
+------+------+
| C | C1 |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+SELECT C, SUM( C1 ) FROM T GROUP BY C;
+------+-----------+
| C | SUM( C1 ) |
+------+-----------+
| 1 | 4 |
+------+-----------+DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE T
( C INT COMMENT 'Beispielspalte ohne Sinn',
C1 INT COMMENT 'Beispielspalte ohne Sinn' )
COMMENT = "sinnlose Beispieltabelle";INSERT INTO T ( C, C1 )
VALUES ( 1, 1 ), ( 2, 2 ), ( 1, 3 ), ( 2, 4 );SELECT * FROM T;
+------+------+
| C | C1 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
| 2 | 4 |
+------+------+SELECT C, SUM( C1 ) FROM T GROUP BY C;
+------+-----------+
| C | SUM( C1 ) |
+------+-----------+
| 1 | 4 |
| 2 | 6 |
+------+-----------+
Eine Multiplikationstabelle als Anwendung des kartesischen Produktes
mysql> drop table n;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE N ( C INT );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO N VALUES ( 0 ), ( 1 ), ( 2 ), ( 3 );
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT N.C, CONCAT( GROUP_CONCAT(( CONCAT( N.C, '*', N1.C, '=', N.C*N1.C) ) ORDER BY N1.C SEPARATOR ' ' )) FROM N, N AS N1 GROUP BY N.C ORDER BY N.C;
+------+------------------------------------------------------------------------------------------------+
| C | CONCAT( GROUP_CONCAT(( CONCAT( N.C, '*', N1.C, '=', N.C*N1.C) ) ORDER BY N1.C SEPARATOR ' ' )) |
+------+------------------------------------------------------------------------------------------------+
| 0 | 0*0=0 0*1=0 0*2=0 0*3=0 |
| 1 | 1*0=0 1*1=1 1*2=2 1*3=3 |
| 2 | 2*0=0 2*1=2 2*2=4 2*3=6 |
| 3 | 3*0=0 3*1=3 3*2=6 3*3=9 |
+------+------------------------------------------------------------------------------------------------+4 rows in set (0.00 sec)
Ein Schachbrett als Anwendung des kartesischen Produktes *
main.sqlWARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;CREATE TABLE R ( R INT );
INSERT INTO R ( R ) VALUES ( 8 ), ( 7 ), ( 6 ), ( 5 ), ( 4 ), ( 3 ), ( 2 ), ( 1 );
SELECT R FROM R;
CREATE TABLE C ( C CHAR( 1 ) );
INSERT INTO C ( C ) VALUES ( 'A' ), ( 'B' ), ( 'C' ), ( 'D' ), ( 'E' ), ( 'F' ), ( 'G' ), ( 'H' );
SELECT C FROM C;
SELECT CONCAT( C, '-', R ) AS K FROM C, R;
SELECT GROUP_CONCAT( CONCAT( C, R )) AS G FROM C, R GROUP BY R;
SELECT CONCAT( GROUP_CONCAT(( CONCAT( C, R, ' ' ) ) ORDER BY C SEPARATOR '' )) AS SCHACHBRETT FROM C, R GROUP BY R ORDER BY R DESC;
- Protokoll
+------+
| R |
+------+
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+------+
+------+
| C |
+------+
| A |
| B |
| C |
| D |
| E |
| F |
| G |
| H |
+------+
+------+
| K |
+------+
| A-8 |
| B-8 |
| C-8 |
| D-8 |
| E-8 |
| F-8 |
| G-8 |
| H-8 |
| A-7 |
| B-7 |
| C-7 |
| D-7 |
| E-7 |
| F-7 |
| G-7 |
| H-7 |
| A-6 |
| B-6 |
| C-6 |
| D-6 |
| E-6 |
| F-6 |
| G-6 |
| H-6 |
| A-5 |
| B-5 |
| C-5 |
| D-5 |
| E-5 |
| F-5 |
| G-5 |
| H-5 |
| A-4 |
| B-4 |
| C-4 |
| D-4 |
| E-4 |
| F-4 |
| G-4 |
| H-4 |
| A-3 |
| B-3 |
| C-3 |
| D-3 |
| E-3 |
| F-3 |
| G-3 |
| H-3 |
| A-2 |
| B-2 |
| C-2 |
| D-2 |
| E-2 |
| F-2 |
| G-2 |
| H-2 |
| A-1 |
| B-1 |
| C-1 |
| D-1 |
| E-1 |
| F-1 |
| G-1 |
| H-1 |
+------+
+-------------------------+
| G |
+-------------------------+
| E1,F1,A1,G1,B1,H1,C1,D1 |
| G2,B2,H2,C2,D2,E2,F2,A2 |
| E3,F3,A3,G3,B3,H3,C3,D3 |
| G4,B4,H4,C4,D4,E4,F4,A4 |
| E5,F5,A5,G5,B5,H5,C5,D5 |
| G6,B6,H6,C6,D6,E6,F6,A6 |
| E7,F7,A7,G7,B7,H7,C7,D7 |
| G8,B8,H8,C8,D8,E8,F8,A8 |
+-------------------------+
+--------------------------+
| SCHACHBRETT |
+--------------------------+
| A8 B8 C8 D8 E8 F8 G8 H8 |
| A7 B7 C7 D7 E7 F7 G7 H7 |
| A6 B6 C6 D6 E6 F6 G6 H6 |
| A5 B5 C5 D5 E5 F5 G5 H5 |
| A4 B4 C4 D4 E4 F4 G4 H4 |
| A3 B3 C3 D3 E3 F3 G3 H3 |
| A2 B2 C2 D2 E2 F2 G2 H2 |
| A1 B1 C1 D1 E1 F1 G1 H1 |
+--------------------------+
Behandlung des Wertes »NULL« *
WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
SET div_precision_increment = 4;
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE T
( C INT COMMENT 'Beispielspalte ohne Sinn' )
COMMENT = "sinnlose Beispieltabelle";INSERT INTO T ( C ) VALUES ( 1 ),( 1 ),( NULL );
SELECT * FROM T;
+------+
| C |
+------+
| 1 |
| 1 |
| NULL |
+------+SELECT C FROM T GROUP BY C;
+------+
| C |
+------+
| NULL |
| 1 |
+------+SELECT DISTINCT C FROM T;
+------+
| C |
+------+
| 1 |
| NULL |
+------+DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE T
( C INT COMMENT 'Beispielspalte ohne Sinn',
C1 INT COMMENT 'Beispielspalte ohne Sinn' )
COMMENT = "sinnlose Beispieltabelle";INSERT INTO T ( C, C1 )
VALUES ( 1, 1 ), ( 1, NULL ), ( NULL, 1 ), ( NULL, NULL );SELECT * FROM T;
+------+------+
| C | C1 |
+------+------+
| 1 | 1 |
| 1 | NULL |
| NULL | 1 |
| NULL | NULL |
+------+------+
Falls mehrere Spalten hinter »GROUP BY« angegeben werden, wird nach deren Verbindung gruppiert. Es werden also alle Zeilen zusammengefaßt, in denen beide Spalten gleiche Werte haben.
SELECT C, C1 FROM T GROUP BY C, C1;
+------+------+
| C | C1 |
+------+------+
| NULL | NULL |
| NULL | 1 |
| 1 | NULL |
| 1 | 1 |
+------+------+SELECT DISTINCT C, C1 FROM T;
+------+------+
| C | C1 |
+------+------+
| 1 | 1 |
| 1 | NULL |
| NULL | 1 |
| NULL | NULL |
+------+------+
Eine Spalte, die nicht zur Gruppierung herangezogen wird, darf nicht in der Auswahlliste genannt werden, da es nicht klar ist, welcher Wert dieser Spalte in der zusammengefaßten Zeile verwendet werden soll.
Einige Datenbanken tolerieren die Angabe einer nicht zur Gruppierung herangezogenen Spalte in der Auswahlliste. Der Wert dieser Spalte in der abgeleiteten Tabelle ist dann aber nicht durch die Regeln von SQL bestimmt.
SELECT C, C1 FROM T GROUP BY C;+------+------+
| C | C1 |
+------+------+
| NULL | 1 |
| 1 | 1 |
+------+------+