SELECT column, aggregate_function(col)
FROM table
WHERE row_filter
GROUP BY column
HAVING group_filter
ORDER BY column
LIMIT n;

-- ERROR: cannot use aggregate function in WHERE
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE COUNT(*) > 5       -- ERROR: Invalid use of group function
GROUP BY customer_id;

-- Correct: use HAVING for aggregate conditions
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- Customers who have placed more than 3 orders
SELECT
  customer_id,
  COUNT(*)             AS order_count,
  ROUND(SUM(total), 2) AS total_spent
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING order_count > 3
ORDER BY total_spent DESC;

-- Product categories with average price above $50
SELECT
  category_id,
  COUNT(*)           AS products,
  ROUND(AVG(price), 2) AS avg_price,
  ROUND(MIN(price), 2) AS min_price,
  ROUND(MAX(price), 2) AS max_price
FROM products
WHERE is_active = 1
GROUP BY category_id
HAVING avg_price > 50
ORDER BY avg_price DESC;

-- Find customers who spent more than $500 total on delivered orders
SELECT
  c.customer_id,
  CONCAT(c.first_name, ' ', c.last_name)  AS name,
  COUNT(o.order_id)                        AS orders,
  ROUND(SUM(o.total), 2)                   AS lifetime_value
FROM customers AS c
JOIN orders    AS o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, name
HAVING SUM(o.total) > 500
ORDER BY lifetime_value DESC;

-- Departments where the average salary exceeds $80,000
SELECT
  department,
  COUNT(*)                  AS headcount,
  ROUND(AVG(salary), 0)     AS avg_salary,
  ROUND(MIN(salary), 0)     AS min_salary,
  ROUND(MAX(salary), 0)     AS max_salary
FROM employees
WHERE is_active = 1
GROUP BY department
HAVING AVG(salary) > 80000
ORDER BY avg_salary DESC;

-- WHERE filters rows first, then HAVING filters groups
SELECT
  c.country,
  COUNT(DISTINCT c.customer_id)         AS customers,
  COUNT(o.order_id)                     AS total_orders,
  ROUND(SUM(o.total), 2)                AS revenue
FROM customers AS c
JOIN orders    AS o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'                   -- row filter (before grouping)
  AND o.created_at >= '2024-01-01'             -- row filter
GROUP BY c.country
HAVING COUNT(DISTINCT c.customer_id) >= 10     -- group filter (after aggregation)
   AND SUM(o.total) > 1000                     -- group filter
ORDER BY revenue DESC;

-- Returns the row only if the table has more than 100 orders total
SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;

-- More practically: return summary stats only if enough data exists
SELECT
  ROUND(AVG(total), 2) AS avg_order,
  COUNT(*)             AS sample_size
FROM orders
WHERE status = 'delivered'
HAVING COUNT(*) >= 30;   -- only report if we have a meaningful sample size

-- MySQL allows referencing SELECT aliases in HAVING
SELECT
  customer_id,
  COUNT(*)             AS order_count,
  ROUND(SUM(total), 2) AS total_spent,
  ROUND(AVG(total), 2) AS avg_order
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING order_count >= 5          -- uses alias defined in SELECT
   AND avg_order   >= 50.00      -- uses alias defined in SELECT
ORDER BY total_spent DESC;

-- Top product categories by revenue in Q1 2024
SELECT
  cat.name                                    AS category,
  COUNT(DISTINCT o.order_id)                  AS orders,
  SUM(oi.quantity)                            AS units,
  ROUND(SUM(oi.quantity * oi.unit_price), 2)  AS revenue
FROM categories  AS cat
JOIN products    AS p   ON cat.category_id = p.category_id
JOIN order_items AS oi  ON p.product_id    = oi.product_id
JOIN orders      AS o   ON oi.order_id     = o.order_id
WHERE o.status = 'delivered'
  AND o.created_at BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY cat.category_id, cat.name
HAVING revenue > 5000
ORDER BY revenue DESC;

-- Countries with more than 5 customers who each spent over $200
SELECT
  c.country,
  COUNT(DISTINCT c.customer_id) AS high_value_customers
FROM customers AS c
JOIN orders    AS o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.country, c.customer_id        -- group per customer first
HAVING SUM(o.total) > 200               -- filter to high-spenders only
-- Now wrap in an outer query to count per country
;

-- Correct two-step approach using a subquery
SELECT country, COUNT(*) AS high_value_customers
FROM (
  SELECT c.country, c.customer_id
  FROM customers AS c
  JOIN orders    AS o ON c.customer_id = o.customer_id
  WHERE o.status = 'delivered'
  GROUP BY c.country, c.customer_id
  HAVING SUM(o.total) > 200
) AS spenders
GROUP BY country
HAVING COUNT(*) >= 5
ORDER BY high_value_customers DESC;

-- Revenue by category, only categories over $10,000, plus grand total
SELECT
  COALESCE(cat.name, 'GRAND TOTAL') AS category,
  ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items AS oi
JOIN products    AS p   ON oi.product_id = p.product_id
JOIN categories  AS cat ON p.category_id = cat.category_id
JOIN orders      AS o   ON oi.order_id   = o.order_id
WHERE o.status = 'delivered'
GROUP BY cat.name WITH ROLLUP
HAVING GROUPING(cat.name) = 1        -- always include the ROLLUP grand total
    OR SUM(oi.quantity * oi.unit_price) > 10000;

-- Slow: filters all rows by country AFTER aggregation
SELECT country, COUNT(*) AS cnt
FROM orders
GROUP BY country
HAVING country = 'Canada';       -- inefficient: scans all rows, then filters

-- Fast: filter country BEFORE aggregation
SELECT country, COUNT(*) AS cnt
FROM orders
WHERE country = 'Canada'         -- efficient: index on country filters first
GROUP BY country;