1.4 - DQL Group und Aggregate.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Kurs Teil 1.4: DQL Group und Aggregatsfunktionen\n",
    "\n",
    "Durch Gruppierung können Datensätze zusammengefasst werden und durch die Aggregatsfunktionen einzelne Spalten in der Gruppe verarbeitet werden.\n",
    "\n",
    "\n",
    "## Syntax\n",
    "\n",
    "```sql\n",
    "SELECT {Resultat-Spalten} FROM {Tabelle} GROUP BY {Spalte nach der gruppiert wird} HAVING {Expression}\n",
    "```\n",
    "\n",
    "\n",
    "### Group By\n",
    "\n",
    "Mehrere Datensätze werden anhand von Spalten gruppiert (gleiche Werte zusammengefasst).\n",
    "\n",
    "```sql\n",
    "SELECT Fach\n",
    "FROM Schueler\n",
    "GROUP BY Fach\n",
    "```\n",
    "\n",
    "Dadurch werden die Datensätze der Tabelle _Schueler_ anhand des Fachs gruppiert. Die restlichen Spalten können nicht mehr eindeutig zurückgegeben werden. Sie müssen aggregiert werden.\n",
    "\n",
    "![](./resources/group.drawio.svg)\n",
    "\n",
    "\n",
    "### Aggregatsfunktionen\n",
    "\n",
    "Werte in Spalten die durch die Gruppierung nicht mehr eindeutig sind, müssen aggregiert werden, bevor sie ausgegeben werden.\n",
    "`HAVING` ermöglicht es nach den gruppierten Werten zu filtern, ähnlich wie `WHERE` bei Abfragen ohne Gruppierung.\n",
    "`WHERE` filtert die Datensätze bevor diese gruppiert wurden, `HAVING` filtert nach der Gruppierung. Die beiden Schlüsselworte können kombiniert verwendet werden.\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "  Fach,                -- Gruppierte Spalte\n",
    "  AVG(Note),           -- Durchschnitt der Noten anhand der Gruppierung\n",
    "  MIN(Note),           -- Tiefste Note anhand der Gruppierung\n",
    "  MAX(Note),           -- Höchste Note anhand der Gruppierung\n",
    "  COUNT(*)             -- Anzahl (ursprünglicher) Datensätze pro Gruppe\n",
    "FROM Schueler\n",
    "GROUP BY Fach\n",
    "  HAVING COUNT(*) > 1  -- Gibt nur Fächer aus, bei denen mehr wie eine Note vorhanden ist\n",
    "```\n",
    "\n",
    "Dadurch werden die Datensätze der Tabelle _Schueler_ anhand des Fachs gruppiert und die Werte des Spalte _Note_ aggregiert.\n",
    "\n",
    "![](./resources/groupaggregate.drawio.svg)\n",
    "\n",
    "\n",
    "Weitere Informationen: [Generation of the set of result rows](https://www.sqlite.org/lang_select.html#resultset)  \n",
    "Weitere Informationen: [Built-in Aggregate Functions](https://www.sqlite.org/lang_aggfunc.html)\n",
    "\n",
    "\n",
    "## Beispiele\n",
    "\n",
    "Alle Namen der Abteilungen mit Mitarbeitenden und deren Anzahl ausgeben:\n",
    "```sql\n",
    "SELECT\n",
    "  a.bezeichnung,\n",
    "  COUNT(*)\n",
    "FROM mitarbeiter m\n",
    "  LEFT JOIN abteilung a ON m.abteilungid = a.id\n",
    "GROUP BY a.bezeichnung\n",
    "```\n",
    "\n",
    "Alle Namen der Abteilungen mit Mitarbeitenden und deren Anzahl ausgeben, bei denen die Anzahl Mitarbeiter grösser wie 2 ist:\n",
    "```sql\n",
    "SELECT\n",
    "  a.bezeichnung,\n",
    "  COUNT(*) AnzahlMitarbeitende\n",
    "FROM mitarbeiter m\n",
    "  LEFT JOIN abteilung a ON m.abteilungid = a.id\n",
    "GROUP BY a.bezeichnung\n",
    "  HAVING AnzahlMitarbeitende > 2  -- HAVING funktioniert auch auf aggregierte Spalten in der Selektion\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aufgaben"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ausführen um die Verbindung zur Datenbank aufzubauen\n",
    "\n",
    "import urllib.request\n",
    "urllib.request.urlretrieve(\"https://github.com/tschuegge/SqlKurs/raw/master/connect.py\", \"connect.py\")\n",
    "def sql(): pass\n",
    "%run ./connect.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aufgabe 1.4.1\n",
    "\n",
    "Erstelle eine Liste mit der Anzahl Kunden pro Ort:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql(\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aufgabe 1.4.2\n",
    "\n",
    "Erstelle eine Liste mit der Anzahl Artikel pro Hersteller"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql(\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aufgabe 1.4.3\n",
    "\n",
    "Erstelle eine Mitarbeitendenliste auf welcher der Nettoumsatz (basierend auf der Liefermenge) pro Mitarbeitenden ausgegeben wird."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql(\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aufgabe 1.4.4\n",
    "\n",
    "Ermittle das Wievielfache des Jahresgehalts (Quote) pro Mitarbeitenden verkauft wurde und sortiere absteigend nach der Quote. Als Grundlage kann die vorherige Liste verwendet werden."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql(\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aufgabe 1.4.5\n",
    "\n",
    "Ermittle den durchschnittlichen Bruttopreis und die Anzahl der Artikel pro Artikelkategorie. Relevant sind nur die Kategorien, welche mehr wie drei Artikel haben und nicht die Kategorie 'Software' sind."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql(\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aufgabe 1.4.6\n",
    "\n",
    "Mit Produkten welcher Hersteller werden weniger wie 500'000 Franken Nettoumsatz oder wird gar nichts geliefert?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql(\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "\"\"\")"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
  },
  "kernelspec": {
   "display_name": "Python 3.8.9 64-bit",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}