🗄️ SQL Lesson 04 — Aggregate Functions

1. Sample Data — orders table

ဒီ lesson မှာ ဒီ table ကို သုံးမယ်:

2. Core Aggregate Functions

-- COUNT — how many rows
SELECT COUNT(*) FROM orders;             -- 6
SELECT COUNT(DISTINCT customer) FROM orders; -- 3 (Ko Min, Ma Aye, Ko Kyaw)

-- SUM — total
SELECT SUM(amount) FROM orders;           -- 2650
SELECT SUM(amount * qty) FROM orders;     -- expression sum

-- AVG — average
SELECT AVG(amount) FROM orders;           -- 441.67

-- MIN / MAX
SELECT MIN(amount) FROM orders;           -- 20
SELECT MAX(amount) FROM orders;           -- 1200

-- Combine them all
SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders;

Result:

3. GROUP BY — Group ခွဲပြီး Aggregate

-- Orders per customer
SELECT customer, COUNT(*) AS num_orders
FROM orders
GROUP BY customer;

-- Total spent per customer
SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC;

-- Sales per city
SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY city;

4. HAVING — Group Filter (WHERE for GROUP BY)

-- WHERE = individual rows filter (before GROUP)
-- HAVING = group result filter (after GROUP)

-- Customers who spent more than 800
SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING total_spent > 800;

-- Cities with more than 1 order
SELECT city, COUNT(*) AS order_count
FROM orders
GROUP BY city
HAVING order_count >= 2;

-- WHERE + GROUP BY + HAVING together
SELECT customer, SUM(amount) AS total
FROM orders
WHERE city != 'Bago'        -- filter rows first
GROUP BY customer
HAVING total > 100          -- filter groups after
ORDER BY total DESC;

5. Aggregate Functions Reference

6. Real-World Example — Sales Report

-- Monthly sales report per city
SELECT
    city,
    COUNT(DISTINCT customer) AS customers,
    COUNT(*) AS total_orders,
    SUM(amount * qty) AS gross_revenue,
    AVG(amount) AS avg_order_value,
    MAX(amount) AS biggest_order
FROM orders
WHERE amount > 0
GROUP BY city
HAVING gross_revenue > 500
ORDER BY gross_revenue DESC;