Intermediate

Aggregation

Summarize data using aggregate functions, GROUP BY for grouping, and HAVING for filtering aggregated results.

Aggregate Functions

Aggregate functions compute a single value from a set of rows:

SQL
SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT user_id) AS unique_customers,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value,
  MIN(total) AS smallest_order,
  MAX(total) AS largest_order
FROM orders;
FunctionDescriptionNULL Handling
COUNT(*)Count all rowsIncludes NULLs
COUNT(col)Count non-NULL valuesExcludes NULLs
SUM(col)Sum of valuesIgnores NULLs
AVG(col)Average of valuesIgnores NULLs
MIN(col)Minimum valueIgnores NULLs
MAX(col)Maximum valueIgnores NULLs

GROUP BY

GROUP BY groups rows that have the same values in specified columns, then applies aggregate functions to each group:

SQL
-- Revenue by category
SELECT category,
       COUNT(*) AS num_products,
       AVG(price) AS avg_price,
       SUM(price * quantity) AS total_revenue
FROM products
GROUP BY category
ORDER BY total_revenue DESC;

-- Monthly order summary
SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS orders,
       SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
⚠
Rule: Every column in your SELECT must either be in the GROUP BY clause or wrapped in an aggregate function. Mixing grouped and ungrouped columns is a common SQL error.

HAVING

HAVING filters groups after aggregation, just like WHERE filters rows before aggregation:

SQL
-- Categories with more than 10 products
SELECT category,
       COUNT(*) AS num_products,
       AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY num_products DESC;

-- Customers who spent more than $1000
SELECT u.name,
       SUM(o.total) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name
HAVING SUM(o.total) > 1000;

WHERE vs HAVING

ClauseFiltersWhen AppliedCan Use Aggregates?
WHEREIndividual rowsBefore GROUP BYNo
HAVINGGroupsAfter GROUP BYYes
SQL
-- Combine WHERE and HAVING
SELECT category,
       COUNT(*) AS num_products,
       AVG(price) AS avg_price
FROM products
WHERE price > 10           -- filter rows first
GROUP BY category
HAVING COUNT(*) >= 5      -- then filter groups
ORDER BY avg_price DESC;

SQL Query Execution Order

💡
SQL executes in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Understanding this order helps you write correct queries and debug errors.