SQL Aggregate Functions and GROUP BY: Power Up Your Reporting - Complete Guide

Aggregate functions transform raw rows into meaningful business metrics, turning transactional data into actionable insights for dashboards, reports, and analytics. Combined with GROUP BY and HAVING, they enable powerful summaries like monthly revenue trends, top-performing products, and customer cohort analysis. This guide covers everything from basic aggregates to production-grade reporting patterns. 

 

What Are Aggregate Functions?

Aggregate functions process multiple rows to produce a single summary value: COUNT for volume, SUM for totals, AVG for averages, MIN/MAX for extremes. They are essential for business intelligence, answering questions like "What's our total revenue?" or "How many active users per subscription plan?"

SUM, COUNT, AVG, MIN, MAX with Examples

Let's use a realistic sales_orders table for examples:

order_id	customer_id	product_name	quantity	unit_price	order_date	status
1001	101	Laptop Pro	1	75000	2025-01-01	delivered
1002	102	Mouse Wireless	2	1500	2025-01-02	delivered
1003	101	Keyboard Mech	1	5000	2025-01-03	pending
1004	103	Monitor 27"	1	25000	2025-01-04	NULL

-- Basic aggregates across entire table
SELECT 
  COUNT(*) as total_orders,
  COUNT(status) as completed_orders,
  SUM(unit_price * quantity) as total_revenue,
  AVG(unit_price) as avg_price,
  MIN(order_date) as first_order,
  MAX(order_date) as latest_order
FROM sales_orders;

4

Total Orders

3

Completed Orders

₹110,500

Total Revenue

₹26,250

Avg Price

Key observation: COUNT(*) counts all rows (4), but COUNT(status) ignores NULL status (3). AVG automatically skips NULLs.

GROUP BY: Turning Rows into Insights

GROUP BY collapses rows sharing the same values in specified columns, applying aggregates to each group.

-- Revenue by product
SELECT 
  product_name,
  COUNT(*) as order_count,
  SUM(quantity) as total_units_sold,
  SUM(unit_price * quantity) as total_revenue
FROM sales_orders
GROUP BY product_name
ORDER BY total_revenue DESC;

product_name	order_count	total_units_sold	total_revenue
Laptop Pro	1	1	₹75,000
Monitor 27"	1	1	₹25,000
Keyboard Mech	1	1	₹5,000
Mouse Wireless	1	2	₹3,000

GROUP BY Rule: Every non-aggregate column in SELECT must appear in GROUP BY, or you'll get an error.

HAVING: Filtering Grouped Results

WHERE filters rows before grouping. HAVING filters after grouping, operating on aggregate values.

-- Products with revenue > ₹10,000 (HAVING on aggregate)
SELECT 
  product_name,
  SUM(unit_price * quantity) as total_revenue
FROM sales_orders
GROUP BY product_name
HAVING total_revenue > 10000
ORDER BY total_revenue DESC;

-- Compare: Filter by customer before grouping (WHERE)
SELECT 
  customer_id,
  COUNT(*) as order_count,
  SUM(unit_price * quantity) as customer_revenue
FROM sales_orders
WHERE status = 'delivered'  -- Filters rows first
GROUP BY customer_id
HAVING order_count >= 1;    -- Filters groups after

Handling NULLs and Edge Cases

• COUNT(*): Counts all rows including NULL
• COUNT(column): Ignores NULL values in that column
• SUM, AVG, MIN, MAX: Ignore NULLs automatically
• Empty groups: Use COALESCE(aggregate, 0) for zero defaults

SELECT 
  customer_id,
  COALESCE(SUM(unit_price * quantity), 0) as revenue,
  COALESCE(COUNT(*), 0) as orders
FROM sales_orders
GROUP BY customer_id
WITH ROLLUP;  -- MySQL: adds summary row

Practical Reporting Queries for Business

1. Monthly Revenue Trend

SELECT 
  DATE_FORMAT(order_date, '%Y-%m') as month,
  COUNT(*) as orders,
  SUM(unit_price * quantity) as revenue,
  AVG(unit_price) as avg_order_value
FROM sales_orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

2. Top 5 Products by Revenue

SELECT 
  product_name,
  SUM(quantity) as units_sold,
  SUM(unit_price * quantity) as revenue
FROM sales_orders
GROUP BY product_name
ORDER BY revenue DESC
LIMIT 5;

3. Customer Lifetime Value by Cohort

SELECT 
  DATE_FORMAT(first_order_date, '%Y-%m') as cohort_month,
  COUNT(DISTINCT customer_id) as unique_customers,
  SUM(total_amount) as total_revenue,
  AVG(total_amount) as avg_customer_value
FROM customer_lifetime_metrics
GROUP BY cohort_month
HAVING unique_customers >= 5
ORDER BY cohort_month;

4. Active Users per Subscription Plan

SELECT 
  plan_name,
  COUNT(*) as active_users,
  SUM(monthly_revenue) as plan_revenue
FROM user_subscriptions
WHERE status = 'active' 
  AND last_login >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY plan_name
ORDER BY active_users DESC;

Performance Tips for Aggregate Queries

• Index GROUP BY columns: CREATE INDEX idx_date ON sales_orders(order_date);
• Filter with WHERE before GROUP BY: Reduces rows to aggregate
• Use summary tables for frequently-run reports:

  CREATE TABLE daily_revenue_summary AS
  SELECT DATE(order_date) as report_date, SUM(total) as daily_revenue
  FROM sales_orders
  GROUP BY DATE(order_date);

• Avoid functions on indexed columns: DATE(order_date) prevents index use
• EXPLAIN your queries: Verify "Using index" and avoid "Using temporary; Using filesort"

Aggregate functions with GROUP BY unlock the true analytical power of SQL, transforming transactional noise into executive-ready insights. Master these patterns and your reporting queries will scale from startup dashboards to enterprise analytics platforms.