Guida a SQL GROUP BY

SELECT group_column, AGGREGATE(value_column)
FROM table_name
GROUP BY group_column;

-- Input: scores
-- player | team | points
-- -------+------+-------
-- Ana    | Red  | 12
-- Ben    | Blue | 9
-- Cleo   | Red  | 7
-- Dev    | Blue | 15
-- Eli    | Red  | 4

SELECT team, SUM(points) AS total_points, COUNT(*) AS players
FROM scores
GROUP BY team;

-- Output:
-- team | total_points | players
-- -----+--------------+--------
-- Red  | 23           | 3
-- Blue | 24           | 2

-- Input: products
-- name    | category
-- --------+---------
-- Blender | Kitchen
-- Toaster | Kitchen
-- Pillow  | Bedroom

SELECT name, category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- ERROR: column "products.name" must appear in the GROUP BY clause
--   or be used in an aggregate function
-- (SQLite runs it anyway and takes name from one row in each group)

-- Input: products
-- name        | category
-- ------------+-----------
-- Notebook    | Stationery
-- Gift Card   | NULL
-- Pen Set     | Stationery
-- Mystery Box | NULL
-- Stapler     | Stationery

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Output:
-- category   | product_count
-- -----------+--------------
-- NULL       | 2
-- Stationery | 3

SELECT COALESCE(category, 'Uncategorized') AS category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Output:
-- category      | product_count
-- --------------+--------------
-- Uncategorized | 2
-- Stationery    | 3

-- Input: orders
-- order_id | amount
-- ---------+-------
-- 1        | 35.00
-- 2        | 240.00
-- 3        | 18.50
-- 4        | 120.00
-- 5        | 64.00

SELECT CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END AS order_size,
       COUNT(*) AS order_count
FROM orders
GROUP BY order_size  -- or GROUP BY 1
ORDER BY order_size;
-- SQL Server allows neither: repeat the CASE expression in GROUP BY

-- Output:
-- order_size | order_count
-- -----------+------------
-- large      | 2
-- small      | 3

-- Input: sales
-- region | category | amount
-- -------+----------+-------
-- East   | Books    | 100.00
-- East   | Toys     | 50.00
-- West   | Books    | 80.00
-- East   | Books    | 40.00

SELECT region, category, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(region, category);
-- MySQL: GROUP BY region, category WITH ROLLUP
-- SQLite has no ROLLUP

-- Output:
-- region | category | total_sales
-- -------+----------+------------
-- East   | Books    | 140.00
-- East   | Toys     | 50.00
-- East   | NULL     | 190.00
-- West   | Books    | 80.00
-- West   | NULL     | 80.00
-- NULL   | NULL     | 270.00

-- Input: categories
-- category_name
-- -------------
-- Audio
-- Cameras
-- Drones

-- Input: products
-- id | name       | category
-- ---+------------+---------
-- 1  | Earbuds    | Audio
-- 2  | Soundbar   | Audio
-- 3  | Action Cam | Cameras

-- Wrong: COUNT(*) counts the NULL row of an empty category
SELECT c.category_name, COUNT(*) AS product_count
FROM categories c
LEFT JOIN products p ON p.category = c.category_name
GROUP BY c.category_name
ORDER BY c.category_name;

-- Output:
-- category_name | product_count
-- --------------+--------------
-- Audio         | 2
-- Cameras       | 1
-- Drones        | 1

-- Right: COUNT(p.id) skips it
SELECT c.category_name, COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p ON p.category = c.category_name
GROUP BY c.category_name
ORDER BY c.category_name;

-- Output:
-- category_name | product_count
-- --------------+--------------
-- Audio         | 2
-- Cameras       | 1
-- Drones        | 0

-- Input: purchases
-- category | amount
-- ---------+-------
-- Books    | 45.00
-- Toys     | 120.00
-- Garden   | 310.00
-- Books    | 80.00
-- Music    | 25.00

SELECT category, SUM(amount) AS revenue
FROM purchases
GROUP BY category
ORDER BY revenue DESC
LIMIT 3;
-- SQL Server has no LIMIT: write SELECT TOP 3 category, ... instead

-- Output:
-- category | revenue
-- ---------+--------
-- Garden   | 310.00
-- Books    | 125.00
-- Toys     | 120.00

-- Input: sales
-- region | sale_date  | amount
-- -------+------------+-------
-- East   | 2024-01-08 | 120.00
-- West   | 2024-01-15 | 80.00
-- East   | 2024-01-22 | 95.00
-- East   | 2024-02-03 | 60.00
-- West   | 2024-02-11 | 150.00

SELECT region, EXTRACT(MONTH FROM sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY region, EXTRACT(MONTH FROM sale_date)
ORDER BY region, month;

-- Output:
-- region | month | total_sales
-- -------+-------+------------
-- East   | 1     | 215.00
-- East   | 2     | 60.00
-- West   | 1     | 80.00
-- West   | 2     | 150.00

-- Input: orders
-- order_id | customer_id | order_date
-- ---------+-------------+-----------
-- 1        | 201         | 2023-11-20
-- 2        | 202         | 2023-12-05
-- 3        | 201         | 2024-01-09
-- 4        | 202         | 2024-02-14
-- 5        | 201         | 2024-03-02

SELECT customer_id, COUNT(*) AS orders_2024
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

-- Output:
-- customer_id | orders_2024
-- ------------+------------
-- 201         | 2