SQL Aggregate Functions: Summarizing Data with Precision

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Total of all salaries
SELECT SUM(salary) AS total_payroll
FROM employees;
 
-- Total with arithmetic expression
SELECT SUM(quantity * unit_price) AS total_revenue
FROM order_items;
 
-- SUM with WHERE clause
SELECT SUM(amount) AS q1_revenue
FROM transactions
WHERE transaction_date >= '2024-01-01'
  AND transaction_date < '2024-04-01';
 
-- Multiple SUMs in one query
SELECT 
    SUM(revenue) AS total_revenue,
    SUM(cost) AS total_cost,
    SUM(revenue) - SUM(cost) AS total_profit,
    SUM(revenue - cost) AS total_profit_alt  -- Mathematically equivalent
FROM monthly_financials;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Sample table for examples
-- bonuses: employee_id, bonus_amount (some NULL)
 
-- Basic SUM ignores NULLs
SELECT SUM(bonus_amount) AS total_bonuses
FROM bonuses;
-- If some bonuses are NULL, they don't reduce the total
 
-- Check for completely NULL data
SELECT 
    SUM(bonus_amount) AS raw_sum,           -- NULL if all NULL
    COALESCE(SUM(bonus_amount), 0) AS safe_sum  -- 0 if all NULL
FROM bonuses;
 
-- Understanding the difference: NULL vs zero
-- Scenario: Employees with no bonus record vs $0 bonus
SELECT 
    e.name,
    b.bonus_amount,
    COALESCE(b.bonus_amount, 0) AS effective_bonus
FROM employees e
LEFT JOIN bonuses b ON e.id = b.employee_id;
 
-- SUM with COALESCE inside (treats NULL as 0)
SELECT SUM(COALESCE(bonus_amount, 0)) AS total_with_nulls_as_zero
FROM bonuses;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Sample data: Values are 10, 20, 20, 30
SELECT 
    SUM(amount) AS regular_sum,              -- 80 (10+20+20+30)
    SUM(DISTINCT amount) AS distinct_sum     -- 60 (10+20+30)
FROM sample_values;
 
-- Practical use: Avoiding double-counting in joins
-- Problem: Order joined to multiple line items
SELECT 
    SUM(o.shipping_cost) AS wrong_total  -- Counted once per line item!
FROM orders o
JOIN order_items oi ON o.id = oi.order_id;
 
-- Solution 1: SUM(DISTINCT) if shipping costs are unique per order
SELECT 
    SUM(DISTINCT o.shipping_cost) AS shipping_total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id;
 
-- Solution 2 (more reliable): Aggregate separately
SELECT 
    (SELECT SUM(shipping_cost) FROM orders) AS shipping_total,
    (SELECT SUM(line_total) FROM order_items) AS items_total;
 
-- Solution 3: Use a subquery or CTE
WITH order_totals AS (
    SELECT id, shipping_cost FROM orders
)
SELECT SUM(shipping_cost) FROM order_totals;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Conditional sum using CASE
SELECT 
    SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END) AS north_revenue,
    SUM(CASE WHEN region = 'South' THEN revenue ELSE 0 END) AS south_revenue,
    SUM(CASE WHEN region = 'East' THEN revenue ELSE 0 END) AS east_revenue,
    SUM(CASE WHEN region = 'West' THEN revenue ELSE 0 END) AS west_revenue
FROM sales;
 
-- Alternative: Use NULL instead of 0 (SUM ignores NULLs anyway)
SELECT 
    SUM(CASE WHEN region = 'North' THEN revenue END) AS north_revenue,
    SUM(CASE WHEN region = 'South' THEN revenue END) AS south_revenue
FROM sales;
-- Same result, slightly cleaner syntax
 
-- Calculate percentages of total
SELECT 
    SUM(revenue) AS total,
    SUM(CASE WHEN product_type = 'Electronics' THEN revenue END) AS electronics,
    SUM(CASE WHEN product_type = 'Electronics' THEN revenue END) * 100.0 
        / NULLIF(SUM(revenue), 0) AS electronics_pct
FROM sales;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- PostgreSQL: FILTER clause for conditional aggregation
SELECT 
    SUM(revenue) AS total_revenue,
    SUM(revenue) FILTER (WHERE region = 'North') AS north_revenue,
    SUM(revenue) FILTER (WHERE region = 'South') AS south_revenue,
    SUM(revenue) FILTER (WHERE transaction_date >= '2024-01-01') AS ytd_revenue
FROM sales;
 
-- Complex conditions with FILTER
SELECT 
    customer_id,
    SUM(amount) AS total_spent,
    SUM(amount) FILTER (WHERE status = 'completed') AS completed_orders,
    SUM(amount) FILTER (WHERE status = 'refunded') AS refunded_amount,
    SUM(amount) FILTER (WHERE category = 'Premium') AS premium_purchases
FROM orders
GROUP BY customer_id;
 
-- FILTER vs CASE performance
-- Generally equivalent, but FILTER can be more readable
-- Some query planners may optimize FILTER differently

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Running totals with conditional sums
SELECT 
    month,
    revenue,
    SUM(CASE WHEN revenue > 0 THEN revenue ELSE 0 END) 
        OVER (ORDER BY month) AS cumulative_positive,
    SUM(CASE WHEN revenue < 0 THEN revenue ELSE 0 END) 
        OVER (ORDER BY month) AS cumulative_losses
FROM monthly_performance;
 
-- Pivot-style reporting
SELECT 
    product_category,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN amount END) AS q1,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 2 THEN amount END) AS q2,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 3 THEN amount END) AS q3,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 4 THEN amount END) AS q4,
    SUM(amount) AS annual_total
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2024
GROUP BY product_category;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Potential overflow scenario
-- If salary is INTEGER and sum exceeds 2 billion:
SELECT SUM(salary) FROM huge_employee_table;
-- Modern databases typically return BIGINT, avoiding overflow
 
-- Explicit casting for safety
SELECT SUM(salary::BIGINT) FROM employees;  -- PostgreSQL
SELECT SUM(CAST(salary AS BIGINT)) FROM employees;  -- Standard SQL
 
-- Financial precision: Use DECIMAL, not FLOAT
-- WRONG: Float precision loss
SELECT SUM(price * 0.1) FROM items;  -- Floating point errors possible
 
-- CORRECT: DECIMAL for exact arithmetic
SELECT SUM(CAST(price AS DECIMAL(15,4)) * 0.1) FROM items;
 
-- Check actual types returned
-- PostgreSQL:
SELECT pg_typeof(SUM(int_column)) FROM sample;  -- Usually returns 'bigint'
SELECT pg_typeof(SUM(numeric_column)) FROM sample;  -- Returns 'numeric'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- Total revenue per product category
SELECT 
    category,
    SUM(revenue) AS category_revenue
FROM sales
GROUP BY category
ORDER BY category_revenue DESC;
 
-- Multiple levels of grouping
SELECT 
    region,
    product_line,
    SUM(units_sold) AS total_units,
    SUM(revenue) AS total_revenue,
    SUM(revenue) / NULLIF(SUM(units_sold), 0) AS avg_price
FROM sales
GROUP BY region, product_line
ORDER BY region, total_revenue DESC;
 
-- Time-series aggregation
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(order_total) AS monthly_revenue,
    SUM(SUM(order_total)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS cumulative_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- ROLLUP: Hierarchical subtotals
SELECT 
    COALESCE(region, 'ALL REGIONS') AS region,
    COALESCE(product_category, 'ALL CATEGORIES') AS category,
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP(region, product_category)
ORDER BY region NULLS FIRST, product_category NULLS FIRST;
-- Results include: each region/category combo, region subtotals, grand total
 
-- CUBE: All possible subtotal combinations
SELECT 
    COALESCE(region, 'ALL') AS region,
    COALESCE(channel, 'ALL') AS channel,
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(region, channel);
-- Results include: each combo, region totals, channel totals, grand total
 
-- GROUPING function to identify subtotal rows
SELECT 
    region,
    product_category,
    SUM(revenue) AS total_revenue,
    GROUPING(region) AS is_region_total,
    GROUPING(product_category) AS is_category_total
FROM sales
GROUP BY ROLLUP(region, product_category);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- Pattern: Running total (cumulative sum)
SELECT 
    transaction_date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS running_total
FROM transactions;
 
-- Pattern: Running total with partition reset
SELECT 
    account_id,
    transaction_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id 
        ORDER BY transaction_date
    ) AS account_running_total
FROM transactions;
 
-- Pattern: Percentage of total
SELECT 
    category,
    SUM(revenue) AS category_revenue,
    SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER () AS pct_of_total
FROM sales
GROUP BY category;
 
-- Pattern: Year-over-year comparison
SELECT 
    product_id,
    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = 2023 THEN amount END) AS sales_2023,
    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = 2024 THEN amount END) AS sales_2024,
    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = 2024 THEN amount END) -
    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = 2023 THEN amount END) AS yoy_change
FROM sales
GROUP BY product_id;