PostgreSQL Aggregate Functions

Aggregate functions in PostgreSQL are used to perform calculations on multiple rows and return a single result. They are commonly used with the GROUP BY clause to summarize data.

1. Common Aggregate Functions

• COUNT(column): Counts the number of rows.
• SUM(column): Returns the sum of values.
• AVG(column): Returns the average value.
• MIN(column): Returns the minimum value.
• MAX(column): Returns the maximum value.
• STRING_AGG(column, separator): Concatenates strings from multiple rows.

2. COUNT Example

-- Count total employees
            SELECT COUNT(*) AS total_employees
            FROM employees;

            -- Count employees in each department
            SELECT department_id, COUNT(*) AS department_count
            FROM employees
            GROUP BY department_id;

3. SUM and AVG Example

-- Total salary of all employees
            SELECT SUM(salary) AS total_salary
            FROM employees;

            -- Average salary per department
            SELECT department_id, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department_id;

4. MIN and MAX Example

-- Highest and lowest salary in the company
            SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary
            FROM employees;

            -- Highest salary per department
            SELECT department_id, MAX(salary) AS max_salary
            FROM employees
            GROUP BY department_id;

5. STRING_AGG Example

PostgreSQL supports STRING_AGG to combine text values from multiple rows into a single string.

-- List employee names per department
            SELECT department_id, STRING_AGG(name, ', ') AS employees
            FROM employees
            GROUP BY department_id;

6. Aggregate Functions with HAVING

The HAVING clause filters groups created by GROUP BY, similar to WHERE for individual rows.

-- Departments with more than 5 employees
            SELECT department_id, COUNT(*) AS total_employees
            FROM employees
            GROUP BY department_id
            HAVING COUNT(*) > 5;

7. Aggregate Functions with JOINs

-- Total salary per department with department names
            SELECT d.department_name, SUM(e.salary) AS total_salary
            FROM employees e
            JOIN departments d ON e.department_id = d.id
            GROUP BY d.department_name
            ORDER BY total_salary DESC;

8. Best Practices

• Use GROUP BY with aggregate functions for meaningful summaries.
• Use HAVING to filter aggregated results instead of WHERE.
• Combine multiple aggregates for richer insights in a single query.
• Consider indexes for large datasets to improve performance of aggregates.

Conclusion

Aggregate functions in PostgreSQL are essential for analyzing and summarizing data. Functions like COUNT, SUM, AVG, MIN, MAX, and STRING_AGG provide powerful tools to extract insights from your data efficiently. Combined with GROUP BY and HAVING, they allow you to create complex summaries, reporting, and analytics. In the next tutorial, we will explore the SQL Playground, which allows testing SQL queries interactively.