Statistikfunktionen in SQL (MySQL )
Aggregatabfragen erstellen Zeilen, deren Werte sich jeweils aus mehreren Zeilen einer zugrundeliegenden Tabelle zusammensetzen können.
Zählen von Zeilen
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 |
+------+
»COUNT( * )« ist die Anzahl der Zeilen einschließlich mehrfacher gleicher Zeilen und Zeilen mit nur NULL.
SELECT COUNT( * ) FROM T;
+------------+
| COUNT( * ) |
+------------+
| 3 |
+------------+
»COUNT( C )« ist die Anzahl der Zeilen einschließlich mehrfacher Zeilen mit dem gleichen Wert in der angegebenen Spalte (hier: »C«), aber ohne Zeilen mit NULL in der angegebenen Spalte (hier: »C«).
SELECT COUNT( C ) FROM T;
+------------+
| COUNT( C ) |
+------------+
| 2 |
+------------+
»COUNT( DISTINCT C )« ist die Anzahl der Zeilen mit unterschiedlichen Werten in der angegebenen Spalte (hier: »C«), ohne Zeilen mit NULL in der angegebenen Spalte (hier: »C«).
SELECT COUNT( DISTINCT C ) FROM T;
+---------------------+
| COUNT( DISTINCT C ) |
+---------------------+
| 1 |
+---------------------+
Weitere Statistikfunktionen
Eine Statistikfunktion oder Aggregatfunktion ermittelt eine statistische Kennzahl – wie die Anzahl – aus Werten, die in der Regel einer bestimmten Spalte entnommen werden.
»COUNT« ist ein Beispiel für eine Aggregatfunktion.
Wie auch bei einigen anderen Funktionen, erlaubt MySQL hier kein Leerzeichen nach dem Funktionsnamen!
Aggregatfunktionen ergeben eine Zeile.
Wie oben gezeigt wurde, zählt »COUNT( * )« alle Zeilen einer Tabelle, einschließlich der Zeilen, die nur NULL enthalten. Alle anderen Anwendungen von Aggregatfunktionen ignorieren NULL-Werte.
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE NOTEN
( ARBEIT INT NOT NULL COMMENT 'Referenz auf Tabelle der Arbeiten',
SCHUELER INT NOT NULL COMMENT 'Referenz auf Tabelle der Schueler',
NOTE INT( 1 ) NOT NULL COMMENT 'Referenz auf Tabelle der Noten' )
COMMENT = 'Der Schueler SCHUELER hat in der Arbeit ARBEIT die Note NOTE erhalten.';INSERT INTO NOTEN ( ARBEIT, SCHUELER, NOTE )
VALUES ( 1, 1, 3 ),
( 1, 2, 6 ),
( 1, 3, 3 ),
( 1, 4, 2 ),
( 1, 5, 1 ),
( 1, 6, 3 ),
( 1, 7, 6 ),
( 1, 8, 4 ),
( 1, 9, 2 );SELECT * FROM NOTEN;
+----------+--------+------+
| SCHUELER | ARBEIT | NOTE |
+----------+--------+------+
| 1 | 1 | 3 |
| 2 | 1 | 6 |
| 3 | 1 | 3 |
| 4 | 1 | 2 |
| 5 | 1 | 1 |
| 6 | 1 | 3 |
| 7 | 1 | 6 |
| 8 | 1 | 4 |
| 9 | 1 | 2 |
+----------+--------+------+SELECT MAX( NOTE ), MIN( NOTE ), AVG( NOTE ), SUM( NOTE ) FROM NOTEN;
+-------------+-------------+-------------+-------------+
| MAX( NOTE ) | MIN( NOTE ) | AVG( NOTE ) | SUM( NOTE ) |
+-------------+-------------+-------------+-------------+
| 6 | 1 | 3.3333 | 30 |
+-------------+-------------+-------------+-------------+SELECT GROUP_CONCAT( NOTE ) FROM NOTEN;
+----------------------+
| GROUP_CONCAT( NOTE ) |
+----------------------+
| 3,6,3,2,1,3,6,4,2 |
+----------------------+SELECT GROUP_CONCAT( NOTE SEPARATOR '' ) FROM NOTEN;
+-----------------------------------+
| GROUP_CONCAT( NOTE SEPARATOR '' ) |
+-----------------------------------+
| 363213642 |
+-----------------------------------+SELECT GROUP_CONCAT( '[', NOTE, ']' SEPARATOR '-' ) FROM NOTEN;
+----------------------------------------------+
| GROUP_CONCAT( '[', NOTE, ']' SEPARATOR '-' ) |
+----------------------------------------------+
| [3]-[6]-[3]-[2]-[1]-[3]-[6]-[4]-[2] |
+----------------------------------------------+DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE T
( C INT COMMENT 'Beispielspalte ohne Sinn' )
COMMENT = 'leere Beispieltabelle';mysql> SELECT COUNT( * ), COUNT( C ), SUM( C ), GROUP_CONCAT( C ) FROM T;
+------------+------------+----------+-------------------+
| COUNT( * ) | COUNT( C ) | SUM( C ) | GROUP_CONCAT( C ) |
+------------+------------+----------+-------------------+
| 0 | 0 | NULL | NULL |
+------------+------------+----------+-------------------+
Bei einer leeren Tabelle ist die Summe einer Spalte NULL, obwohl die Summe über die leere Menge in der Mathematik die Zahl 0 ist. Auch alle anderen Statistikfunktionen ergeben bei einer leeren Tabelle NULL, nur »COUNT« ergibt richtig 0. Mathematisch sollte eine Anwendung einer Operation auf die leere Menge das neutrale Element dieser Operation ergeben, bei einer Zeichenverkettung also beispielsweise die leere Kette.
Aggregatfunktionen
HELP Functions and Modifiers for Use with GROUP BY
You asked for help about help category: "Functions and Modifiers for Use with GROUP BY"
For more information, type 'help <item>', where <item> is one of the followingtopics:
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNT DISTINCT
GROUP_CONCAT
MAX
MIN
STD
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VARIANCE
VAR_POP
VAR_SAMP
Statistikfunktionen und Spaltennamen
Die Tabelle der Planeten soll um eine Spalte mit der Gesamtmasse ergänzt werden.
Bei Verwendung einer Statistikfunktion in der Auswahlliste erscheint aber nur noch eine Zeile. Dabei wird ein willkürlicher Wert für die Werte der Spaltennamen verwenden.
SELECT NAME, MASSE, SUM( MASSE ) FROM PLANET;
+--------+----------+-----------------------+
| NAME | MASSE | SUM( MASSE ) |
+--------+----------+-----------------------+
| Merkur | 3.302e23 | 2.6688346000000003e27 |
+--------+----------+-----------------------+
Übungsaufgaben
Übungsaufgaben
/ Tabellen erweitern
- Skript (MySQL )
WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
SET div_precision_increment = 4;
DROP SCHEMA S; CREATE SCHEMA S; USE S;CREATE TABLE POSTEN ( NAME VARCHAR( 255 ), ANZAHL INTEGER, WERT INTEGER, PRIMARY KEY( NAME ));
INSERT INTO POSTEN ( NAME, ANZAHL, WERT ) VALUES ( 'Tisch', 1, 2800 );
INSERT INTO POSTEN ( NAME, ANZAHL, WERT ) VALUES ( 'Stuhl', 6, 720 );
Erweitern Sie die Tabelle »POSTEN« um den Gesamtwert, die Mehrwertsteuer und den Bruttobetrag für jeden Posten.
(Das heißt: Schreiben Sie eine Abfrage, welche eine neue abgeleitete Tabelle ergibt, die sechs Spalten hat: Die drei Spalten der ursprünglichen Tabelle und zusätzlich jeweils eine Spalte mit dem Gesamtwert der in der Zeile angegebenen Ware (Anzahl mal Wert), der Mehrwertsteuer zu diesem Gesamtwert und dem Bruttobetrag als Summe aus Gesamtwert und der Mehrwertsteuer. Hier soll keine neue Basistabelle angelegt werden, sondern nur eine abgeleitete Tabelle.)
Legen Sie eine Ansicht-Tabelle mit der von Ihnen geschrieben Abfrage an.
Ermitteln Sie dann mit einer weiteren Abfrage die Summen der hinzugefügten Spalten.
(Das heißt: Schreiben Sie eine Abfrage, welche eine neue abgeleitete Tabelle ergibt, die drei Spalten hat: Eine Spalte mit der Summe der Gesamtwerte der einzelnen Posten, eine Spalte mit der Summe der Mehrwertsteuern der einzelnen Posten und eine Spalte mit der Summe der Bruttobeträge der einzelnen Posten.)
Zählen von Zeilen ⃗
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 |
+------+------+SELECT COUNT( * ) FROM T;
+------------+
| COUNT( * ) |
+------------+
| 4 |
+------------+SELECT COUNT( C ) FROM T;
+------------+
| COUNT( C ) |
+------------+
| 2 |
+------------+SELECT COUNT( C1 ) FROM T;
+-------------+
| COUNT( C1 ) |
+-------------+
| 2 |
+-------------+SELECT COUNT( DISTINCT C ) FROM T;
+---------------------+
| COUNT( DISTINCT C ) |
+---------------------+
| 1 |
+---------------------+SELECT COUNT( DISTINCT C1 ) FROM T;
+----------------------+
| COUNT( DISTINCT C1 ) |
+----------------------+
| 1 |
+----------------------+