অ্যাগ্রিগেট, GROUP BY ও HAVING

SELECT COUNT(*)              AS customers,      -- সারির সংখ্যা
       COUNT(city)           AS with_city,      -- যেসব সারিতে city NULL নয়
       COUNT(DISTINCT city)  AS cities          -- NULL বাদে আলাদা আলাদা মান
FROM customers;

+-----------+-----------+--------+
| customers | with_city | cities |
+-----------+-----------+--------+
|         8 |         7 |      5 |
+-----------+-----------+--------+

SELECT COUNT(*)                          AS products,
       COUNT(stock)                      AS with_stock,
       SUM(stock)                        AS total_stock,
       AVG(stock)                        AS avg_stock,
       ROUND(AVG(COALESCE(stock, 0)), 2) AS avg_if_null_were_0,
       MIN(price)                        AS cheapest,
       MAX(price)                        AS dearest
FROM products;

+----------+------------+-------------+-----------+--------------------+----------+---------+
| products | with_stock | total_stock | avg_stock | avg_if_null_were_0 | cheapest | dearest |
+----------+------------+-------------+-----------+--------------------+----------+---------+
|       11 |          8 |         165 |    20.625 |               15.0 |      900 |   18500 |
+----------+------------+-------------+-----------+--------------------+----------+---------+

SELECT COUNT(*)                AS products,
       SUM(price)              AS sum_price,
       COALESCE(SUM(price), 0) AS sum_or_zero
FROM products
WHERE category_id = 5;

+----------+-----------+-------------+
| products | sum_price | sum_or_zero |
+----------+-----------+-------------+
|        0 | NULL      |           0 |
+----------+-----------+-------------+

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

+-----------+--------+
| status    | orders |
+-----------+--------+
| delivered |     11 |
| cancelled |      1 |
| pending   |      1 |
| shipped   |      1 |
+-----------+--------+

orders (14 rows)            split by status                 aggregate each pile
-----------------           -------------------------       --------------------
1 delivered          ──►    delivered: 1 2 3 4 6 7 8   ──►  delivered   11
2 delivered                            9 10 11 14
...                         cancelled: 5               ──►  cancelled    1
13 pending                  pending:   13              ──►  pending      1
14 delivered                shipped:   12              ──►  shipped      1

SELECT order_id,
       COUNT(*)                   AS lines,
       SUM(quantity)              AS units,
       SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_id;

+----------+-------+-------+-------------+
| order_id | lines | units | order_total |
+----------+-------+-------+-------------+
|        1 |     2 |     2 |        2100 |
|        2 |     1 |     1 |        4500 |
|        3 |     1 |     1 |        3000 |
|        4 |     2 |     2 |        4000 |
|        5 |     1 |     1 |       18500 |
|        6 |     1 |     1 |       15000 |
|        7 |     2 |     3 |        4000 |
|        8 |     1 |     2 |        2400 |
|        9 |     2 |     2 |        4950 |
|       10 |     1 |     1 |       18500 |
|       11 |     2 |     2 |        5500 |
|       12 |     1 |     2 |        3000 |
|       13 |     1 |     1 |       15000 |
|       14 |     2 |     2 |        3100 |
+----------+-------+-------+-------------+

SELECT product_id,
       SUM(quantity)              AS units,
       SUM(quantity * unit_price) AS revenue
FROM order_items
GROUP BY product_id
ORDER BY revenue DESC, product_id
LIMIT 5;

+------------+-------+---------+
| product_id | units | revenue |
+------------+-------+---------+
|          5 |     2 |   37000 |
|          7 |     2 |   30000 |
|          3 |     2 |    8550 |
|          6 |     2 |    6000 |
|          2 |     2 |    5000 |
+------------+-------+---------+

SELECT customer_id, status, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

+-------------+-----------+--------+
| customer_id | status    | orders |
+-------------+-----------+--------+
|           1 | delivered |      3 |
|           2 | delivered |      2 |
|           2 | shipped   |      1 |
|           3 | delivered |      3 |
|           4 | cancelled |      1 |
|           5 | delivered |      1 |
|           6 | delivered |      1 |
|           6 | pending   |      1 |
|           7 | delivered |      1 |
+-------------+-----------+--------+

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY customer_id;

+-------------+--------+
| customer_id | orders |
+-------------+--------+
|           1 |      3 |
|           2 |      3 |
|           3 |      3 |
+-------------+--------+

SELECT customer_id, COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY customer_id;

+-------------+------------------+
| customer_id | delivered_orders |
+-------------+------------------+
|           1 |                3 |
|           3 |                3 |
+-------------+------------------+

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE COUNT(*) >= 3
GROUP BY customer_id;

Error: misuse of aggregate: COUNT()

-- PostgreSQL
SELECT customer_id, COUNT(*) AS n
FROM orders
GROUP BY customer_id
HAVING n >= 3;

Error: column "n" does not exist

-- PostgreSQL
SELECT customer_id, order_date, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;

Error: column "orders.order_date" must appear in the GROUP BY clause or be used in an aggregate function

-- MySQL
SELECT customer_id, order_date, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;

Error: ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.orders.order_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT customer_id, order_date, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 3;

+-------------+------------+--------+
| customer_id | order_date | orders |
+-------------+------------+--------+
|           1 | 2026-01-12 |      3 |
|           2 | 2026-01-20 |      3 |
|           3 | 2026-02-14 |      3 |
+-------------+------------+--------+

SELECT SUM(quantity * unit_price)                     AS gross,
       COUNT(DISTINCT order_id)                       AS orders,
       ROUND(AVG(quantity * unit_price), 2)           AS avg_line_value,
       ROUND(SUM(quantity * unit_price) * 1.0
             / COUNT(DISTINCT order_id), 2)           AS avg_order_value
FROM order_items;

+--------+--------+----------------+-----------------+
| gross  | orders | avg_line_value | avg_order_value |
+--------+--------+----------------+-----------------+
| 103550 |     14 |         5177.5 |         7396.43 |
+--------+--------+----------------+-----------------+

SELECT COUNT(*) AS payments, SUM(amount) AS received FROM payments;

+----------+----------+
| payments | received |
+----------+----------+
|       13 |    70050 |
+----------+----------+

import pandas as pd
from sqlhelp import con

by_cat = pd.read_sql_query("""
    SELECT category_id, COUNT(*) AS products, AVG(price) AS avg_price
    FROM products
    GROUP BY category_id
    ORDER BY category_id
""", con)
print(by_cat)
print("average of the averages:", by_cat["avg_price"].mean())
print("true average price:     ", round(pd.read_sql_query("SELECT AVG(price) AS a FROM products", con)["a"][0], 2))

   category_id  products  avg_price
0          NaN         1     1000.0
1          1.0         2     1850.0
2          2.0         4     7850.0
3          3.0         2     9000.0
4          4.0         2     1850.0
average of the averages: 4310.0
true average price:      5254.55

-- সহজ
SELECT category_id, COUNT(*) AS products, ROUND(AVG(price)) AS avg_price
FROM products
GROUP BY category_id
ORDER BY category_id;

+-------------+----------+-----------+
| category_id | products | avg_price |
+-------------+----------+-----------+
| NULL        |        1 |    1000.0 |
|           1 |        2 |    1850.0 |
|           2 |        4 |    7850.0 |
|           3 |        2 |    9000.0 |
|           4 |        2 |    1850.0 |
+-------------+----------+-----------+

-- মাঝারি
SELECT customer_id,
       COUNT(*)        AS orders,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY orders DESC, customer_id;

+-------------+--------+-------------+------------+
| customer_id | orders | first_order | last_order |
+-------------+--------+-------------+------------+
|           1 |      3 | 2026-01-12  | 2026-04-19 |
|           2 |      3 | 2026-01-20  | 2026-05-21 |
|           3 |      3 | 2026-02-14  | 2026-06-09 |
|           6 |      2 | 2026-04-02  | 2026-06-02 |
+-------------+--------+-------------+------------+

-- কঠিন
SELECT order_id,
       COUNT(*)                   AS lines,
       SUM(quantity)              AS units,
       SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
HAVING COUNT(*) > 1 AND SUM(quantity * unit_price) >= 4000
ORDER BY order_total DESC, order_id;

SELECT method, COUNT(*) AS payments, SUM(amount) AS total, ROUND(AVG(amount), 2) AS avg_amount
FROM payments
GROUP BY method
HAVING SUM(amount) > 10000
ORDER BY total DESC;

+----------+-------+-------+-------------+
| order_id | lines | units | order_total |
+----------+-------+-------+-------------+
|       11 |     2 |     2 |        5500 |
|        9 |     2 |     2 |        4950 |
|        4 |     2 |     2 |        4000 |
|        7 |     2 |     3 |        4000 |
+----------+-------+-------+-------------+
+--------+----------+-------+------------+
| method | payments | total | avg_amount |
+--------+----------+-------+------------+
| card   |        6 | 44950 |    7491.67 |
| bkash  |        5 | 18000 |     3600.0 |
+--------+----------+-------+------------+