{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GROUP BY, donde SQL empieza a responder\n",
    "\n",
    "Resumir 900 filas en cuatro números, y la regla que PostgreSQL te exige y SQLite te deja pasar.\n",
    "\n",
    "Cuaderno de soluciones del capítulo 6 de **SQL desde cero**, de Miss Yera.\n",
    "\n",
    "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n",
    "\n",
    "Capítulo completo: https://missyera.com/guias/sql-desde-cero/group-by/\n",
    "\n",
    "Este es el cuaderno de **soluciones**. Trae el código de cada ejercicio, la\n",
    "explicación de la trampa y la respuesta del quiz. Si vienes del cuaderno de\n",
    "práctica sin haberlo intentado, vuelve 🙂"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Se baja la base y se deja lista una función `q()` que corre las consultas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n",
    "\n",
    "    Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n",
    "    traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n",
    "    \"\"\"\n",
    "    resultado = None\n",
    "    trozo = \"\"\n",
    "    for linea in sql.splitlines(keepends=True):\n",
    "        trozo += linea\n",
    "        if sqlite3.complete_statement(trozo):\n",
    "            if trozo.strip():\n",
    "                cur = con.execute(trozo.strip())\n",
    "                resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                          columns=[d[0] for d in cur.description])\n",
    "                             if cur.description else None)\n",
    "            trozo = \"\"\n",
    "    if trozo.strip():\n",
    "        cur = con.execute(trozo.strip())\n",
    "        resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                  columns=[d[0] for d in cur.description])\n",
    "                     if cur.description else None)\n",
    "    con.commit()\n",
    "    return resultado\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hasta aquí SQL te ha listado filas. A partir de este capítulo empieza a\n",
    "responder preguntas, que es otra cosa 🌟\n",
    "\n",
    "\"¿Cuánto vendimos por canal?\" no se contesta con una lista de 900 pedidos.\n",
    "Se contesta con cuatro números. Y el que convierte 900 filas en 4 se llama\n",
    "`GROUP BY`.\n",
    "\n",
    "Es también el capítulo donde los motores dejan de ser parecidos. PostgreSQL y\n",
    "SQL Server te van a exigir una cosa que SQLite te deja pasar sin decir nada, y\n",
    "esa diferencia es la que hace que una consulta que funcionaba en tu laptop\n",
    "devuelva otro número en la nube.\n",
    "\n",
    "Y aprovecho para preguntarte: **¿cuántas filas tiene la tabla más grande que has abierto?** Con GROUP BY el tamaño deja de importar, porque ya no miras filas, miras respuestas 📊"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Primero, resumir todo junto"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Las cinco funciones de resumen, que en los manuales vas a ver como\n",
    "**funciones de agregación**, son las mismas en los cuatro motores, y\n",
    "con esas cinco se hace el 90% de los reportes. Las agregaciones y las\n",
    "agrupaciones van siempre juntas: una calcula y la otra decide sobre qué."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS pedidos,\n",
    "       ROUND(SUM(monto), 2) AS total,\n",
    "       ROUND(AVG(monto), 2) AS ticket,\n",
    "       MIN(monto) AS minimo,\n",
    "       MAX(monto) AS maximo\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Una sola fila para las 900. `COUNT` cuenta, `SUM` suma,\n",
    "`AVG` promedia, `MIN` y `MAX` son el más chico y\n",
    "el más grande.\n",
    "\n",
    "Y un detalle del capítulo 4 que aquí se vuelve importante: hay 900 pedidos\n",
    "pero solo 873 tienen monto, porque 27 vienen nulos. `COUNT(*)` cuenta\n",
    "filas y `AVG` ignora los nulos, así que ese ticket de 610,14 es el\n",
    "promedio de 873, no de 900."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ahora, resumir por grupos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal,\n",
    "       COUNT(*) AS pedidos,\n",
    "       ROUND(SUM(monto), 2) AS total\n",
    "FROM pedidos\n",
    "GROUP BY canal\n",
    "ORDER BY total DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cuatro filas, una por canal. Eso es todo lo que hace\n",
    "`GROUP BY`: **junta las filas que tienen el mismo valor en esa\n",
    "columna y calcula el resumen dentro de cada montón**.\n",
    "\n",
    "Web va primero pero por poquito: 140 mil contra 137 mil de WhatsApp. Si\n",
    "alguien te pide \"el canal ganador\", ese margen de menos del 2% merece la\n",
    "conversación de si de verdad hay un ganador 🤔\n",
    "\n",
    "Puedes agrupar por más de una columna, y ahí los montones se parten."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, segmento, COUNT(*) AS clientes\n",
    "FROM clientes\n",
    "GROUP BY ciudad, segmento\n",
    "ORDER BY ciudad, clientes DESC\n",
    "LIMIT 8;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y puedes agrupar por algo *calculado*, que es donde se pone bueno. El\n",
    "mes no es una columna de la tabla: lo fabricamos con lo del capítulo 5."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT STRFTIME('%Y-%m', fecha) AS mes,\n",
    "       COUNT(*) AS pedidos,\n",
    "       ROUND(SUM(monto), 2) AS soles\n",
    "FROM pedidos\n",
    "WHERE fecha >= '2026-01-01'\n",
    "GROUP BY mes\n",
    "ORDER BY mes;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese es el reporte mensual entero, en seis líneas de SQL. Mayo manda en\n",
    "pedidos y en soles, febrero es el más flojo en los dos, y ahí ya tienes algo que\n",
    "contarle a alguien."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La regla que PostgreSQL exige y SQLite no"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta es la parte importante del capítulo. Mira esta consulta y dime qué\n",
    "esperas que salga en la columna `monto`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal, monto, COUNT(*) AS pedidos\n",
    "FROM pedidos\n",
    "GROUP BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "¿El monto de qué pedido? Web tiene 234 pedidos y aquí sale un monto solo,\n",
    "sin que nadie te diga de cuál de los 234 salió.\n",
    "\n",
    "Es que la pregunta no tiene sentido. Le pediste a la base que junte 234 filas\n",
    "en una y que además te dé \"el monto\", en singular. SQLite, en vez de decírtelo,\n",
    "elige uno cualquiera y sigue como si nada.\n",
    "\n",
    "Y para que no quede duda de que es cualquiera: la misma columna, la misma\n",
    "tabla, filtrando solo Web."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal, monto, MIN(monto) AS el_mas_bajo, MAX(monto) AS el_mas_alto, COUNT(*) AS pedidos\n",
    "FROM pedidos\n",
    "WHERE canal = 'Web'\n",
    "GROUP BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Otro número. Misma data, mismo canal, y el `monto` cambió solo\n",
    "porque cambió cómo escribí la consulta. Ese valor no significa nada y ningún\n",
    "motor te va a garantizar cuál te toca.\n",
    "\n",
    "**La regla de verdad, la del estándar, es esta: todo lo que pongas en\n",
    "el `SELECT` tiene que estar o dentro de una función de resumen o\n",
    "dentro del `GROUP BY`.** Ninguna otra cosa."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Poner en el SELECT una columna que no está en el GROUP BY\n",
    "\n",
    "| PostgreSQL | `ERROR: column \"pedidos.monto\" must appear in the GROUP BY clause` |\n",
    "|---|---|\n",
    "| MySQL | `ERROR desde 5.7, que trae ONLY_FULL_GROUP_BY activo de fábrica` |\n",
    "| SQL Server | `ERROR: Column 'pedidos.monto' is invalid in the select list` |\n",
    "| SQLite | `lo permite y devuelve un valor cualquiera del grupo` |\n",
    "\n",
    "Este es EL choque del capítulo. Aprendes con SQLite, te funciona, lo llevas a la base de la empresa y no corre. Y la versión mala es al revés: traes una consulta vieja de MySQL 5.6 que sí corría, y ahora te da un número distinto sin explicación. Escribe siempre como si el motor fuera estricto, aunque el tuyo no lo sea."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El grupo que nadie invitó: los nulos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vamos a sacar el ranking de clientes por número de pedidos, que es de las\n",
    "consultas más pedidas del mundo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id_cliente, COUNT(*) AS pedidos\n",
    "FROM pedidos\n",
    "GROUP BY id_cliente\n",
    "ORDER BY pedidos DESC\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El mejor cliente de la tienda es… una celda vacía, con 24 pedidos 😅\n",
    "\n",
    "Esos 24 son los pedidos que tienen `id_cliente` en\n",
    "`NULL`, y **`GROUP BY` les arma su propio\n",
    "grupo**: todos los nulos caen juntos, como si \"no se sabe\" fuera un\n",
    "cliente más. Eso es igual en los cuatro motores.\n",
    "\n",
    "Y fíjate el daño que hace: si mandas ese ranking sin mirarlo, el número uno\n",
    "de tu tabla no existe. La solución es decidir qué quieres, no dejar que lo\n",
    "decida la base."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id_cliente, COUNT(*) AS pedidos, ROUND(SUM(monto), 2) AS total\n",
    "FROM pedidos\n",
    "WHERE id_cliente IS NOT NULL\n",
    "GROUP BY id_cliente\n",
    "HAVING COUNT(*) >= 14\n",
    "ORDER BY total DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahí sí: cuatro clientes con 14 pedidos o más, y el 58 destacado con 17\n",
    "pedidos y S/10.131.\n",
    "\n",
    "Y una más, que engaña a mucha gente."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS pedidos_con_cliente,\n",
    "       COUNT(DISTINCT id_cliente) AS clientes_que_compraron\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "119 clientes distintos, no 120. Uno de los 120 de la tabla\n",
    "`clientes` no compró nunca, y `COUNT(DISTINCT)` tampoco\n",
    "cuenta el nulo. Dos huecos que se compensan y por eso el número parece\n",
    "razonable 🕵️‍♀️"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## WHERE filtra filas, HAVING filtra grupos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los dos filtran, y la diferencia es *cuándo*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal, COUNT(*) AS pedidos, ROUND(SUM(monto), 2) AS total\n",
    "FROM pedidos\n",
    "WHERE monto > 800\n",
    "GROUP BY canal\n",
    "ORDER BY total DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Eso es \"de los pedidos que pasan de S/800, agrúpame por canal\". El\n",
    "`WHERE` tira filas **antes** de armar los montones, así\n",
    "que WhatsApp gana con 59 pedidos grandes aunque en total venda menos que Web."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, COUNT(*) AS clientes\n",
    "FROM clientes\n",
    "GROUP BY ciudad\n",
    "HAVING COUNT(*) >= 20\n",
    "ORDER BY clientes DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Eso es \"agrúpame por ciudad y quédate solo con las ciudades que tengan 20\n",
    "clientes o más\". El `HAVING` tira montones **después**\n",
    "de armarlos.\n",
    "\n",
    "Por eso `HAVING` puede usar `COUNT(*)` y\n",
    "`WHERE` no. Pruébalo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    SELECT ciudad, COUNT(*) AS clientes\n",
    "    FROM clientes\n",
    "    WHERE COUNT(*) > 20\n",
    "    GROUP BY ciudad;\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "OperationalError: misuse of aggregate: COUNT()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*misuse of aggregate*. Cuando el `WHERE` hace su trabajo,\n",
    "los grupos todavía no existen, así que no hay nada que contar. No es un capricho\n",
    "de la sintaxis, es el orden en que pasan las cosas.\n",
    "\n",
    "### El orden real, que explica casi todo\n",
    "\n",
    "Tú escribes la consulta en un orden y la base la ejecuta en otro. Este es el\n",
    "de verdad, y es igual en los cuatro motores:\n",
    "\n",
    "- 1️⃣ `FROM`, que trae las filas\n",
    "\n",
    "- 2️⃣ `WHERE`, que tira filas\n",
    "\n",
    "- 3️⃣ `GROUP BY`, que arma los montones\n",
    "\n",
    "- 4️⃣ `HAVING`, que tira montones\n",
    "\n",
    "- 5️⃣ `SELECT`, que recién ahí calcula las columnas y los alias\n",
    "\n",
    "- 6️⃣ `ORDER BY`, que ordena\n",
    "\n",
    "- 7️⃣ `LIMIT`, que corta\n",
    "\n",
    "Con esa lista al lado se explican solas tres cosas que confunden: por qué el\n",
    "`WHERE` no ve los alias del `SELECT` (paso 2 contra paso\n",
    "5), por qué el `ORDER BY` sí los ve (paso 6), y por qué\n",
    "`HAVING` puede contar y `WHERE` no."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Usar el alias del SELECT en el HAVING\n",
    "\n",
    "| PostgreSQL | `no lo acepta: hay que repetir COUNT(*)` |\n",
    "|---|---|\n",
    "| MySQL | `lo acepta` |\n",
    "| SQL Server | `no lo acepta` |\n",
    "| SQLite | `lo acepta` |\n",
    "\n",
    "Dos sí y dos no, y esto es hermano del alias en el WHERE del capítulo 3. Si repites la función en el HAVING, funciona en los cuatro y no tienes que acordarte de nada."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Contar bien: las tres formas de COUNT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal,\n",
    "       COUNT(*) AS filas,\n",
    "       COUNT(monto) AS con_monto,\n",
    "       COUNT(DISTINCT id_cliente) AS clientes_distintos\n",
    "FROM pedidos\n",
    "GROUP BY canal\n",
    "ORDER BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`COUNT(*)` cuenta filas, `COUNT(columna)` cuenta valores\n",
    "que no son nulos, y `COUNT(DISTINCT columna)` cuenta valores\n",
    "distintos. Tres números distintos en la misma fila, y cada uno contesta una\n",
    "pregunta distinta:\n",
    "\n",
    "- 📦 ¿Cuántos pedidos entraron por Web? 234.\n",
    "\n",
    "- 💰 ¿De cuántos sé el monto? 228.\n",
    "\n",
    "- 👥 ¿Cuántos clientes distintos compraron por Web? 105.\n",
    "\n",
    "La cantidad de veces que he visto un reporte que dice \"clientes\" y está\n",
    "contando pedidos… muchas 🙈 Cuando el número te salga sospechosamente redondo o\n",
    "sospechosamente alto, revisa cuál de los tres `COUNT` pusiste."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Contar solo algunos, dentro del grupo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta es la técnica que convierte un `GROUP BY` en una tabla de\n",
    "Excel con columnas, y una vez que la ves ya no la sueltas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT STRFTIME('%Y', fecha) AS anio,\n",
    "       ROUND(SUM(CASE WHEN canal = 'Web' THEN monto ELSE 0 END), 2) AS web,\n",
    "       ROUND(SUM(CASE WHEN canal = 'WhatsApp' THEN monto ELSE 0 END), 2) AS whatsapp,\n",
    "       ROUND(SUM(CASE WHEN canal = 'Tienda' THEN monto ELSE 0 END), 2) AS tienda,\n",
    "       ROUND(SUM(CASE WHEN canal = 'Marketplace' THEN monto ELSE 0 END), 2) AS marketplace\n",
    "FROM pedidos\n",
    "GROUP BY anio\n",
    "ORDER BY anio;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los canales pasaron de ser filas a ser columnas. Eso en Excel es una tabla\n",
    "dinámica y aquí es un `CASE WHEN` dentro del `SUM`: suma\n",
    "el monto cuando el canal es el que quiero y suma cero cuando no.\n",
    "\n",
    "`CASE WHEN ... THEN ... ELSE ... END` son los\n",
    "**condicionales** de SQL, el equivalente del SI de Excel o del\n",
    "`if` de Python, y se escriben igual en los cuatro motores. Funcionan\n",
    "en cualquier sitio donde vaya un valor: en el `SELECT`, en el\n",
    "`WHERE` y en el `ORDER BY`.\n",
    "\n",
    "Ojo con leer el 2026: son solo seis meses, así que la mitad de 2025 es lo que\n",
    "hay que comparar, no el año entero."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Contar solo las filas que cumplen algo, dentro del grupo\n",
    "\n",
    "| PostgreSQL | `COUNT(*) FILTER (WHERE monto > 800)` |\n",
    "|---|---|\n",
    "| MySQL | `SUM(monto > 800)   -- o el CASE WHEN de siempre` |\n",
    "| SQL Server | `COUNT(CASE WHEN monto > 800 THEN 1 END)` |\n",
    "| SQLite | `COUNT(*) FILTER (WHERE monto > 800)   -- desde la versión 3.30` |\n",
    "\n",
    "FILTER es del estándar, se lee precioso y está en PostgreSQL y en SQLite. SQL Server y MySQL no lo tienen. El COUNT(CASE WHEN ... THEN 1 END) es más feo y funciona en los cuatro, así que es el que escribo cuando no sé dónde va a terminar corriendo la consulta."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Juntar los valores de un grupo en un texto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, GROUP_CONCAT(DISTINCT segmento) AS segmentos\n",
    "FROM clientes\n",
    "WHERE ciudad IN ('Lima', 'Cusco')\n",
    "GROUP BY ciudad\n",
    "ORDER BY ciudad;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En vez de resumir a un número, pega todos los valores del grupo en una sola\n",
    "celda. Sirve muchísimo para revisar datos: de un vistazo ves qué hay dentro de\n",
    "cada montón."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pegar los valores de un grupo separados por coma\n",
    "\n",
    "| PostgreSQL | `STRING_AGG(segmento, ', ')` |\n",
    "|---|---|\n",
    "| MySQL | `GROUP_CONCAT(segmento SEPARATOR ', ')` |\n",
    "| SQL Server | `STRING_AGG(segmento, ', ')   -- desde 2017` |\n",
    "| SQLite | `GROUP_CONCAT(segmento, ', ')` |\n",
    "\n",
    "Dos nombres para lo mismo, y encima MySQL pide la palabra SEPARATOR donde SQLite pide una coma. De todas las diferencias del libro, esta es la que más veces he tenido que buscar."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Agrupar por el número de la columna en vez de repetirla\n",
    "\n",
    "| PostgreSQL | `GROUP BY 1` |\n",
    "|---|---|\n",
    "| MySQL | `GROUP BY 1` |\n",
    "| SQL Server | `no lo soporta: hay que repetir la expresión entera` |\n",
    "| SQLite | `GROUP BY 1` |\n",
    "\n",
    "Tres de cuatro lo aceptan y es cómodo cuando la expresión es larga, tipo GROUP BY STRFTIME('%Y-%m', fecha). Pero en una consulta que otra persona va a leer, el número no dice nada y la expresión sí."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subtotales y total general en la misma consulta\n",
    "\n",
    "| PostgreSQL | `GROUP BY ROLLUP(ciudad, segmento)` |\n",
    "|---|---|\n",
    "| MySQL | `GROUP BY ciudad, segmento WITH ROLLUP` |\n",
    "| SQL Server | `GROUP BY ROLLUP(ciudad, segmento)` |\n",
    "| SQLite | `no lo tiene: se arma con dos consultas y un UNION ALL` |\n",
    "\n",
    "ROLLUP te agrega las filas de subtotal por ciudad y la de total general, que es justo lo que pide un gerente. SQLite es el único que se queda fuera, así que si lo necesitas ahí, toca UNION ALL a mano."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Siete sobre `tienda.db`. Intenta antes de abrir 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Clientes por ciudad\n",
    "\n",
    "Cuántos clientes hay en cada ciudad, de mayor a menor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, COUNT(*) AS clientes\n",
    "FROM clientes\n",
    "GROUP BY ciudad\n",
    "ORDER BY clientes DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "ciudad    clientes\n",
    "--------  --------\n",
    "Chiclayo  25\n",
    "Piura     24\n",
    "Arequipa  22\n",
    "Trujillo  17\n",
    "Cusco     17\n",
    "Lima      15\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Chiclayo y Piura arriba, Lima última con 15. Contraintuitivo, y por eso vale\n",
    "la pena mirarlo antes de asumir que el negocio está en Lima 🇵🇪"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Ticket promedio por canal\n",
    "\n",
    "El ticket promedio de cada canal, y de paso cuántos montos\n",
    "nulos esconde cada uno."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal, ROUND(AVG(monto), 2) AS ticket, COUNT(*) AS filas, COUNT(monto) AS con_monto\n",
    "FROM pedidos\n",
    "GROUP BY canal\n",
    "ORDER BY ticket DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "canal        ticket  filas  con_monto\n",
    "-----------  ------  -----  ---------\n",
    "Web          615.99  234    228\n",
    "WhatsApp     613.19  231    225\n",
    "Marketplace  611.5   229    222\n",
    "Tienda       598.41  206    198\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los cuatro tickets están entre 598 y 616, o sea que el canal casi no cambia\n",
    "cuánto gasta la gente: lo que cambia es cuántos pedidos entran por cada uno. Un\n",
    "hallazgo de los buenos, porque cambia dónde invertir 💡"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. El catálogo por categoría\n",
    "\n",
    "Cuántos productos y qué precio promedio tiene cada\n",
    "categoría."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT categoria, COUNT(*) AS productos, ROUND(AVG(precio), 2) AS precio_medio\n",
    "FROM productos\n",
    "GROUP BY categoria\n",
    "ORDER BY precio_medio DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "categoria         productos  precio_medio\n",
    "----------------  ---------  ------------\n",
    "Cuidado personal  4          64.27\n",
    "Bebidas           7          59.78\n",
    "Snacks            8          52.43\n",
    "Abarrotes         9          40.88\n",
    "Limpieza          12         35.64\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Limpieza es la categoría con más productos y la más barata; Cuidado personal\n",
    "es la de menos productos y la más cara. Con 40 productos en total, ojo con sacar\n",
    "conclusiones de las categorías de 4."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Los productos que más facturan\n",
    "\n",
    "Los 5 productos con más soles vendidos, usando la tabla\n",
    "`detalle`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id_producto, SUM(cantidad) AS unidades, ROUND(SUM(cantidad * precio_unit), 2) AS soles\n",
    "FROM detalle\n",
    "GROUP BY id_producto\n",
    "ORDER BY soles DESC\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "id_producto  unidades  soles\n",
    "-----------  --------  --------\n",
    "16           857       71490.94\n",
    "17           832       67500.16\n",
    "27           741       66208.35\n",
    "36           796       66044.12\n",
    "40           928       64792.96\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mira que de estos cinco, el producto 40 es el que más unidades mueve (928)\n",
    "y aun así queda último en soles, porque es el más barato. Unidades y soles son\n",
    "dos rankings distintos y casi siempre te piden el equivocado 📊"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Cuántas líneas por pedido\n",
    "\n",
    "Cuántas líneas de detalle hay en total y a cuántos pedidos\n",
    "distintos pertenecen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS lineas, COUNT(DISTINCT id_pedido) AS pedidos_con_detalle\n",
    "FROM detalle;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "lineas  pedidos_con_detalle\n",
    "------  -------------------\n",
    "2682    900\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.682 líneas repartidas en los 900 pedidos, o sea unas 3 líneas por pedido.\n",
    "Y ese `COUNT(DISTINCT id_pedido)` de 900 es una comprobación de\n",
    "integridad gratis: si hubiera salido 899, habría un pedido con detalle\n",
    "huérfano."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Alta de clientes por segmento\n",
    "\n",
    "Por segmento: cuántos clientes, cuándo entró el primero y\n",
    "cuándo el último."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT segmento, COUNT(*) AS clientes, MIN(fecha_alta) AS primero, MAX(fecha_alta) AS ultimo\n",
    "FROM clientes\n",
    "GROUP BY segmento\n",
    "ORDER BY clientes DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "segmento    clientes  primero     ultimo\n",
    "----------  --------  ----------  ----------\n",
    "Horeca      36        2025-01-15  2026-02-04\n",
    "Bodega      35        2025-01-05  2026-01-31\n",
    "Minimarket  25        2025-01-04  2025-12-16\n",
    "Mayorista   24        2025-01-12  2026-01-30\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`MIN` y `MAX` sobre una fecha en texto funcionan porque\n",
    "el formato es AAAA-MM-DD, que ordena igual como fecha y como texto. Con\n",
    "`'24/06/2026'` te habrían dado cualquier cosa.\n",
    "\n",
    "Y ahí ves algo: Minimarket no suma un cliente nuevo desde diciembre. Eso es\n",
    "una pregunta para el área comercial, no una respuesta."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Escríbelo para los cuatro\n",
    "\n",
    "Sin ejecutar: por ciudad, cuántos clientes y la lista de\n",
    "sus segmentos separados por coma, solo para las ciudades con 20 o más\n",
    "clientes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "-- PostgreSQL\n",
    "SELECT ciudad, COUNT(*) AS clientes, STRING_AGG(DISTINCT segmento, ', ') AS segmentos\n",
    "FROM clientes GROUP BY ciudad HAVING COUNT(*) >= 20 ORDER BY clientes DESC;\n",
    "\n",
    "-- MySQL\n",
    "SELECT ciudad, COUNT(*) AS clientes, GROUP_CONCAT(DISTINCT segmento SEPARATOR ', ') AS segmentos\n",
    "FROM clientes GROUP BY ciudad HAVING COUNT(*) >= 20 ORDER BY clientes DESC;\n",
    "\n",
    "-- SQL Server\n",
    "SELECT ciudad, COUNT(*) AS clientes, STRING_AGG(segmento, ', ') AS segmentos\n",
    "FROM clientes GROUP BY ciudad HAVING COUNT(*) >= 20 ORDER BY COUNT(*) DESC;\n",
    "\n",
    "-- SQLite\n",
    "SELECT ciudad, COUNT(*) AS clientes, GROUP_CONCAT(DISTINCT segmento) AS segmentos\n",
    "FROM clientes GROUP BY ciudad HAVING COUNT(*) >= 20 ORDER BY clientes DESC;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tres cosas cambian entre las cuatro: el nombre de la función, cómo se pide el\n",
    "separador, y que en SQL Server el `ORDER BY` tampoco acepta el alias\n",
    "cuando hay `GROUP BY`, así que hay que repetir el\n",
    "`COUNT(*)`. Y el `STRING_AGG` de SQL Server no admite\n",
    "`DISTINCT`, que es la cuarta 🫠"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dos promedios del mismo dato, y no son iguales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Antes de cerrar el capítulo, la trampa que separa un promedio bien hecho de uno que parece bien hecho. Las dos consultas están bien escritas 💸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Sacas el ticket medio de los pedidos. Sumas y divides entre el número de pedidos, que es lo que significa un promedio.\n",
    "\n",
    "```\n",
    "SELECT ROUND(SUM(monto) / COUNT(*), 2) FROM pedidos;\n",
    "-- 591.84\n",
    "\n",
    "SELECT ROUND(AVG(monto), 2) FROM pedidos;\n",
    "-- 610.14\n",
    "```\n",
    "\n",
    "**Qué está mal**\n",
    "\n",
    "Dieciocho soles de diferencia en el mismo promedio 💸\n",
    "\n",
    "`COUNT(*)` cuenta **filas**: 900. `AVG(monto)` y `SUM(monto)` ignoran los nulos, así que trabajan sobre 873. O sea que la primera consulta divide la suma de 873 pedidos entre 900, y el promedio sale bajo.\n",
    "\n",
    "Ninguna de las dos está mal escrita. La pregunta es cuál contesta lo que quieres: si los 27 pedidos sin monto son ventas que existieron y no se registraron, el promedio de verdad está entre las dos y hay que ir a preguntar. Si son pedidos anulados, no deberían estar en el cálculo ni contarse.\n",
    "\n",
    "Por eso `COUNT(*)` y `COUNT(columna)` son dos funciones distintas y conviene escribir siempre la que dice lo que quieres decir. Y por eso el primer `GROUP BY` de cualquier análisis mío lleva las dos al lado, para ver de una si hay nulos escondidos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Cuentas con SELECT ciudad, COUNT(satisfaccion) FROM ventas GROUP BY ciudad y los totales por ciudad no suman el total de la tabla.\n",
    "\n",
    "a) COUNT de una columna no cuenta los nulos, y COUNT(*) sí\n",
    "\n",
    "b) Falta un HAVING para incluir todos los grupos\n",
    "\n",
    "c) El GROUP BY se está comiendo filas repetidas\n",
    "\n",
    "d) Hay ciudades escritas de dos formas y por eso no cuadra\n",
    "\n",
    "---\n",
    "\n",
    "**La correcta es la a.**\n",
    "\n",
    "*b)* El HAVING filtra grupos, y aquí no estás filtrando ninguno.\n",
    "\n",
    "*c)* El GROUP BY agrupa, no descarta. Todas las filas caen en algún grupo.\n",
    "\n",
    "*d)* Eso descuadraría los grupos, no el total. Aquí faltan filas dentro de los grupos que sí existen.\n",
    "\n",
    "COUNT(*) cuenta filas y COUNT(columna) cuenta valores que hay. La diferencia son los huecos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 📦 `GROUP BY` junta las filas con el mismo valor y resume cada\n",
    "montón. Cinco funciones te dan casi todo: `COUNT`, `SUM`,\n",
    "`AVG`, `MIN`, `MAX`.\n",
    "\n",
    "- ⚖️ Todo lo del `SELECT` va dentro de una función de resumen o\n",
    "dentro del `GROUP BY`. PostgreSQL, MySQL y SQL Server te lo exigen;\n",
    "SQLite te deja pasar y te devuelve un valor cualquiera.\n",
    "\n",
    "- 🕳️ Los nulos forman su propio grupo y pueden encabezar tu ranking. Aquí eran\n",
    "24 pedidos sin cliente.\n",
    "\n",
    "- 🚦 `WHERE` filtra filas antes de agrupar; `HAVING`\n",
    "filtra grupos después. Por eso `WHERE COUNT(*)` da error.\n",
    "\n",
    "- 🔢 `COUNT(*)`, `COUNT(columna)` y\n",
    "`COUNT(DISTINCT columna)` son tres preguntas distintas.\n",
    "\n",
    "- 🎛️ `SUM(CASE WHEN ... THEN ... ELSE 0 END)` convierte filas en\n",
    "columnas y funciona en los cuatro. `FILTER` es más bonito pero solo\n",
    "está en PostgreSQL y SQLite.\n",
    "\n",
    "- 🔗 Pegar los valores de un grupo es `STRING_AGG` en PostgreSQL y\n",
    "SQL Server, `GROUP_CONCAT` en MySQL y SQLite.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`COUNT(*)` cuenta filas. `COUNT(columna)` cuenta\n",
    "datos. Casi nunca son el mismo número."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y cuando este resumen tenga que verlo alguien que no escribe consultas,\n",
    "el siguiente paso es un tablero. La\n",
    "[guía de Power BI](https://missyera.com/guias/power-bi-desde-cero/) arranca justo\n",
    "donde termina un GROUP BY 📈\n",
    "\n",
    "En el capítulo 7 llegan los `JOIN`, que es lo que nos falta para\n",
    "poder decir \"el ticket promedio por ciudad\": el ticket está en\n",
    "`pedidos` y la ciudad está en `clientes`, y hasta ahora no\n",
    "sabemos juntarlas.\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 6 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/group-by/\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}