PostgreSQL Aggregate Functions

Common Aggregate Functions

SELECT COUNT(*) FROM users;

SELECT SUM(price) FROM products;

SELECT AVG(rating) FROM reviews;

SELECT MAX(salary) FROM employees;

🔹 COUNT()

Count the number of rows or non-NULL values in a result set. COUNT(*) counts all rows including NULLs, while COUNT(column) counts only non-NULL values. Essential for understanding data volume and completeness.

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

-- Count non-NULL values
SELECT COUNT(email) FROM employees;
-- Returns: 145

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

-- Count with condition
SELECT 
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders
FROM orders;
                            

🔹 SUM()

Calculate the total sum of numeric values. SUM adds up all non-NULL values in a column. Perfect for calculating totals like revenue, quantities, or any cumulative numeric data.

-- Sum all values
SELECT SUM(amount) FROM orders;
-- Returns: 125000.00

-- Sum with condition
SELECT SUM(amount) FROM orders
WHERE status = 'completed';
-- Returns: 98000.00

-- Multiple sums
SELECT 
    SUM(amount) AS total_revenue,
    SUM(cost) AS total_cost,
    SUM(amount - cost) AS total_profit
FROM orders;
                            

🔹 AVG()

Calculate the average (mean) of numeric values. AVG sums all non-NULL values and divides by the count. Useful for finding typical values, performance metrics, or central tendencies in your data.

-- Calculate average
SELECT AVG(salary) FROM employees;
-- Returns: 65000.00

-- Average with rounding
SELECT ROUND(AVG(rating), 2) FROM reviews;
-- Returns: 4.35

-- Average by group
SELECT 
    department,
    ROUND(AVG(salary), 2) AS avg_salary,
    ROUND(AVG(years_experience), 1) AS avg_experience
FROM employees
GROUP BY department;
                            

🔹 MAX() and MIN()

Find the maximum or minimum value in a set. MAX returns the largest value, MIN returns the smallest. Works with numbers, dates, and text. Essential for finding extremes, ranges, or boundaries in your data.

-- Find maximum
SELECT MAX(salary) FROM employees;
-- Returns: 150000.00

-- Find minimum
SELECT MIN(salary) FROM employees;
-- Returns: 35000.00

-- Find range
SELECT 
    MAX(price) AS highest_price,
    MIN(price) AS lowest_price,
    MAX(price) - MIN(price) AS price_range
FROM products;

-- Latest and earliest dates
SELECT 
    MAX(order_date) AS latest_order,
    MIN(order_date) AS first_order
FROM orders;
                            

🔹 GROUP BY with Aggregates

Combine aggregate functions with GROUP BY to calculate statistics for each group. This powerful combination lets you analyze data by categories, time periods, or any grouping criteria you define.

-- Group by single column
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Group by multiple columns
SELECT 
    department,
    job_title,
    COUNT(*) AS count,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department, job_title;

-- Group by date
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY month
ORDER BY month;
                            

🔹 HAVING Clause

Filter grouped results using aggregate conditions. HAVING works like WHERE but for grouped data. Use it after GROUP BY to filter groups based on aggregate calculations like counts or averages.

-- Filter groups by count
SELECT 
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- Filter by average
SELECT 
    department,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Multiple conditions
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) >= 5 AND AVG(price) < 100;
                            

🔹 STRING_AGG()

Concatenate values from multiple rows into a single string with a delimiter. STRING_AGG is perfect for creating comma-separated lists, combining tags, or aggregating text data from related records.

-- Concatenate with comma
SELECT STRING_AGG(name, ', ') AS employee_names
FROM employees
WHERE department = 'Engineering';
-- Returns: John, Alice, Bob, Sarah

-- Concatenate with custom delimiter
SELECT 
    department,
    STRING_AGG(name, ' | ' ORDER BY name) AS employees
FROM employees
GROUP BY department;

-- Create tag lists
SELECT 
    product_id,
    STRING_AGG(tag_name, ', ') AS tags
FROM product_tags
GROUP BY product_id;
                            

🔹 ARRAY_AGG()

Collect values from multiple rows into a PostgreSQL array. ARRAY_AGG creates an array containing all values from the grouped rows. Useful for JSON generation or when you need to work with sets of values.

-- Create array of values
SELECT ARRAY_AGG(name) AS employee_array
FROM employees
WHERE department = 'Engineering';
-- Returns: {John, Alice, Bob, Sarah}

-- Array with ordering
SELECT 
    department,
    ARRAY_AGG(name ORDER BY salary DESC) AS employees_by_salary
FROM employees
GROUP BY department;

-- Collect IDs
SELECT 
    customer_id,
    ARRAY_AGG(order_id) AS order_ids
FROM orders
GROUP BY customer_id;
                            

🔹 BOOL_AND() and BOOL_OR()

Perform logical AND or OR operations across multiple boolean values. BOOL_AND returns true only if all values are true, BOOL_OR returns true if any value is true. Great for checking conditions across groups.

-- Check if all are true
SELECT BOOL_AND(is_active) AS all_active
FROM employees
WHERE department = 'Engineering';
-- Returns: true or false

-- Check if any are true
SELECT BOOL_OR(has_certification) AS any_certified
FROM employees
WHERE department = 'Engineering';

-- Group checks
SELECT 
    department,
    BOOL_AND(is_active) AS all_active,
    BOOL_OR(is_manager) AS has_manager
FROM employees
GROUP BY department;