SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

What is it?

Aggregate functions compute a single summary value from multiple rows -- counting orders, summing revenue, averaging scores. GROUP BY computes them per group (per user, per category, per month).

Why does it matter?

Aggregates are the backbone of every report and dashboard. Without them you would fetch millions of rows to PHP and calculate totals manually -- completely impractical for any real application.

Learn aggregate functions, GROUP BY to group results, and HAVING to filter groups.

Real-World Use Cases

📊 Revenue dashboard - SUM(total) grouped by MONTH(created_at) shows monthly revenue -- one query replaces thousands of PHP calculations.
⭐ Product ratings - AVG(rating) per product gives average star rating. COUNT(rating) gives review count for display.
🏆 Top customers - SUM(total) GROUP BY user_id ORDER BY sum DESC LIMIT 10 -- find top 10 spenders in one query.
📦 Low stock alerts - MIN(stock) and COUNT(*) WHERE stock < 10 -- find products running low on inventory.

Basic Aggregate Functions

SELECT COUNT(*)     AS total_orders   FROM orders;
SELECT COUNT(phone) AS with_phone      FROM users;    -- excludes NULL
SELECT SUM(total)   AS total_revenue   FROM orders;
SELECT AVG(total)   AS avg_order_value FROM orders;
SELECT MIN(price)   AS cheapest,
       MAX(price)   AS most_expensive  FROM products;

GROUP BY -- Aggregate Per Group

SELECT   user_id,
         COUNT(*)   AS order_count,
         SUM(total) AS total_spent
FROM     orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT    10;

SELECT   category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM     products
GROUP BY category;

HAVING -- Filter Groups After Aggregation

-- Customers who spent more than 5000 total
SELECT   user_id, SUM(total) AS total_spent
FROM     orders
GROUP BY user_id
HAVING   total_spent > 5000
ORDER BY total_spent DESC;

-- WHERE filters ROWS before grouping
-- HAVING filters GROUPS after aggregation
-- You CANNOT use SUM/COUNT etc. in WHERE -- use HAVING

Q: What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. You cannot use aggregate functions like SUM() or COUNT() in WHERE -- put them in HAVING instead.

Quick Access

Basic Aggregate Functions

GROUP BY -- Aggregate Per Group

HAVING -- Filter Groups After Aggregation

Related Posts

Comments (0)

Leave a Comment