PostgreSQL Aggregations & Grouping

1. GROUP BY

Groups rows that have the same values into summary rows. Essential for aggregation.

sql

-- Basic GROUP BY
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

-- Multiple grouping columns
SELECT department, is_active, COUNT(*) as count
FROM employees
GROUP BY department, is_active;

-- With expressions
SELECT EXTRACT(YEAR FROM hire_date) as hire_year, 
       COUNT(*) as hires
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date);

-- ORDER BY with GROUP BY
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Important Rules:

• Every column in SELECT must either be:

  1. In the GROUP BY clause

  2. An aggregate function argument

  3. Functionally dependent on GROUP BY columns

Correct:

sql

SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department;  -- department is in GROUP BY

Incorrect (will error):

sql

SELECT department, name, COUNT(*)  -- name is not aggregated or grouped
FROM employees
GROUP BY department;

2. HAVING

Filters groups (like WHERE filters rows). Applied after aggregation.

sql

-- Filter groups based on aggregate values
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;  -- Only departments with >5 employees

-- Multiple HAVING conditions
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 
   AND COUNT(*) >= 3;

-- Using aliases in HAVING (PostgreSQL allows this)
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING emp_count > 10;

-- Complex conditions
SELECT department, 
       MAX(salary) as max_salary,
       MIN(salary) as min_salary
FROM employees
GROUP BY department
HAVING MAX(salary) - MIN(salary) > 30000;  -- Salary range > 30k

WHERE vs HAVING:

sql

-- WHERE filters rows BEFORE grouping
-- HAVING filters groups AFTER grouping

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE is_active = true           -- Only active employees
GROUP BY department
HAVING AVG(salary) > 60000;      -- Departments with avg salary > 60k

3. Aggregate Functions

COUNT

Counts rows or non-NULL values.

sql

-- Count all rows
SELECT COUNT(*) FROM employees;

-- Count non-NULL values in a column
SELECT COUNT(department) FROM employees;  -- NULLs excluded

-- Count distinct values
SELECT COUNT(DISTINCT department) FROM employees;

-- Multiple counts in one query
SELECT 
    COUNT(*) as total_employees,
    COUNT(DISTINCT department) as unique_departments,
    COUNT(bonus) as employees_with_bonus  -- NULLs excluded
FROM employees;

SUM

Calculates total sum (NULL values ignored).

sql

-- Basic SUM
SELECT SUM(salary) as total_salary_payout
FROM employees;

-- SUM with WHERE
SELECT 
    department,
    SUM(salary) as dept_salary_total
FROM employees
WHERE is_active = true
GROUP BY department;

-- SUM with expressions
SELECT SUM(salary * 1.1) as total_with_raise
FROM employees;

-- SUM returns NULL for empty groups
SELECT department, SUM(bonus) as total_bonus
FROM employees
GROUP BY department;

AVG

Calculates average (NULL values excluded from calculation).

sql

-- Basic average
SELECT AVG(salary) as average_salary FROM employees;

-- AVG with precision
SELECT 
    ROUND(AVG(salary), 2) as avg_salary_rounded,
    AVG(salary)::NUMERIC(10,2) as avg_salary_cast
FROM employees;

-- Average of expression
SELECT AVG(salary * 1.05) as avg_with_proposed_raise
FROM employees;

-- Watch out for NULL handling
SELECT 
    AVG(bonus) as avg_bonus,  -- Only non-NULL bonuses considered
    SUM(bonus) / COUNT(*) as avg_including_nulls  -- Different result!
FROM employees;

MIN & MAX

Find minimum and maximum values.

sql

-- Basic MIN/MAX
SELECT 
    MIN(salary) as lowest_salary,
    MAX(salary) as highest_salary
FROM employees;

-- MIN/MAX with dates
SELECT 
    MIN(hire_date) as first_hire,
    MAX(hire_date) as latest_hire
FROM employees;

-- MIN/MAX with strings (alphabetical)
SELECT 
    MIN(name) as first_alphabetically,
    MAX(name) as last_alphabetically
FROM employees;

-- MIN/MAX in groups
SELECT 
    department,
    MIN(salary) as min_salary,
    MAX(salary) as max_salary,
    MAX(salary) - MIN(salary) as salary_range
FROM employees
GROUP BY department;

4. Advanced Aggregation Techniques

Filtered Aggregates (PostgreSQL 9.4+)

sql

-- Aggregate only specific rows within groups
SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(*) FILTER (WHERE is_active = true) as active_employees,
    AVG(salary) FILTER (WHERE salary > 50000) as avg_high_salary,
    SUM(salary) FILTER (WHERE hire_date >= '2023-01-01') as new_hire_salary_total
FROM employees
GROUP BY department;

Multiple Aggregations with CASE

sql

-- Conditional aggregation (pre-FILTER method)
SELECT 
    department,
    COUNT(*) as total,
    SUM(CASE WHEN is_active = true THEN 1 ELSE 0 END) as active_count,
    AVG(CASE WHEN gender = 'M' THEN salary END) as avg_male_salary,
    AVG(CASE WHEN gender = 'F' THEN salary END) as avg_female_salary
FROM employees
GROUP BY department;

Statistical Aggregates

sql

-- PostgreSQL provides statistical functions
SELECT 
    department,
    STDDEV(salary) as salary_stddev,
    VARIANCE(salary) as salary_variance,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_salary,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) as p75_salary
FROM employees
GROUP BY department;

GROUPING SETS

sql

-- Multiple levels of aggregation in one query
SELECT 
    COALESCE(department, 'All Departments') as department,
    COALESCE(EXTRACT(YEAR FROM hire_date)::TEXT, 'All Years') as hire_year,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY GROUPING SETS (
    (department, hire_year),  -- By department and year
    (department),             -- By department only
    (hire_year),              -- By year only
    ()                        -- Grand total
)
ORDER BY department, hire_year;

CUBE & ROLLUP

sql

-- ROLLUP: hierarchical aggregates
SELECT 
    department,
    EXTRACT(YEAR FROM hire_date) as hire_year,
    COUNT(*) as count
FROM employees
GROUP BY ROLLUP(department, hire_year);

-- CUBE: all possible combinations
SELECT 
    department,
    is_active,
    COUNT(*) as count
FROM employees
GROUP BY CUBE(department, is_active);

5. Practical Examples & Patterns

Employee Analytics Report

sql

-- Comprehensive employee report
SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(DISTINCT job_title) as unique_titles,
    SUM(salary) as total_salary_budget,
    ROUND(AVG(salary), 2) as average_salary,
    MIN(salary) as minimum_salary,
    MAX(salary) as maximum_salary,
    ROUND(AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date))), 1) as avg_years_service,
    COUNT(*) FILTER (WHERE is_active = false) as inactive_count
FROM employees
WHERE hire_date >= '2010-01-01'  -- Filter before grouping
GROUP BY department
HAVING COUNT(*) >= 3  -- Only departments with 3+ employees
ORDER BY total_salary_budget DESC;

Department Salary Analysis

sql

-- Salary quartiles by department
SELECT 
    department,
    COUNT(*) as emp_count,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) as q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) as q3,
    ROUND(AVG(salary), 2) as mean,
    ROUND(STDDEV(salary), 2) as std_dev
FROM employees
WHERE salary IS NOT NULL
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY median DESC;

Monthly Hiring Trends

sql

-- Hiring trends over time
SELECT 
    EXTRACT(YEAR FROM hire_date) as year,
    EXTRACT(MONTH FROM hire_date) as month,
    COUNT(*) as hires,
    AVG(salary) as avg_starting_salary,
    SUM(salary) as monthly_salary_added
FROM employees
WHERE hire_date IS NOT NULL
GROUP BY EXTRACT(YEAR FROM hire_date), EXTRACT(MONTH FROM hire_date)
HAVING COUNT(*) > 0
ORDER BY year, month;

Department Comparison

sql

-- Compare departments
SELECT 
    department,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct_of_total,
    ROUND(AVG(salary), 2) as avg_salary,
    ROUND(AVG(salary) - AVG(AVG(salary)) OVER (), 2) as diff_from_overall_avg,
    RANK() OVER (ORDER BY AVG(salary) DESC) as salary_rank
FROM employees
WHERE is_active = true
GROUP BY department
ORDER BY avg_salary DESC;

6. Common Pitfalls & Solutions

1. NULL Handling in Aggregates

sql

-- Problem: NULLs are ignored in AVG, SUM, etc.
SELECT 
    AVG(bonus) as avg_bonus,  -- Only non-NULL bonuses
    AVG(COALESCE(bonus, 0)) as avg_with_zeros  -- Different!
FROM employees;

-- Solution: Be explicit
SELECT 
    department,
    COUNT(bonus) as employees_with_bonus,
    COUNT(*) as total_employees,
    ROUND(100.0 * COUNT(bonus) / COUNT(*), 2) as pct_with_bonus
FROM employees
GROUP BY department;

2. Dividing by Zero

sql

-- Problem: Division by zero error
SELECT 
    department,
    SUM(sales) / COUNT(*) as avg_sales  -- Could error if COUNT(*) = 0
FROM sales_data
GROUP BY department;

-- Solution: NULLIF
SELECT 
    department,
    SUM(sales) / NULLIF(COUNT(*), 0) as safe_avg_sales
FROM sales_data
GROUP BY department;

3. Performance with Large Groups

sql

-- Add indexes on GROUP BY columns
CREATE INDEX idx_employees_dept ON employees(department);
CREATE INDEX idx_employees_hire_date ON employees(hire_date);

-- Use WHERE to reduce data before grouping
SELECT department, AVG(salary)
FROM employees
WHERE hire_date >= '2020-01-01'  -- Filter first
GROUP BY department;

4. Understanding GROUP BY with JOINs

sql

-- Correct aggregation with joins
SELECT 
    d.department_name,
    COUNT(e.id) as employee_count,
    AVG(e.salary) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name;  -- Group by primary key

7. Performance Tips

1. Filter Early: Use WHERE to reduce rows before grouping

2. Index GROUP BY columns: Speeds up grouping operations

3. Limit Result Size: Use HAVING to filter groups early

4. **Avoid SELECT ***: Only select needed columns

5. Use Approximations: For large datasets

   sql

   SELECT COUNT(*) FROM huge_table;  -- Slow
   SELECT reltuples FROM pg_class WHERE relname = 'huge_table';  -- Estimate

Quick Reference