PostgreSQL – GROUP BY Clause

PostgreSQL – GROUP BY

The GROUP BY clause in PostgreSQL is used to group rows that have the same values in specified columns into summary rows. It is typically used with aggregate functions like COUNT, SUM, AVG, MAX, or MIN to perform calculations on each group of rows.

This tutorial walks you through creating a database, table, inserting data, and using the GROUP BY clause with various scenarios.

Step 1: Create a Database

First, create a database named company to hold our data.

CREATE DATABASE company;

Connect to the database:

\c company;

Step 2: Create a Table

Create a table named employees to store employee details.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    experience INT
);

Step 3: Insert Data

Insert sample data into the employees table.

INSERT INTO employees (department, salary, experience)
VALUES 
('IT', 70000, 5),
('HR', 60000, 3),
('IT', 80000, 7),
('Finance', 75000, 6),
('HR', 65000, 4),
('Finance', 72000, 5),
('IT', 85000, 10);

Step 4: Basic Usage of GROUP BY

Group employees by department and calculate the total salary for each department:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

This query returns the total salary paid in each department.

Step 5: Using GROUP BY with COUNT

Count the number of employees in each department:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

This query shows how many employees work in each department.

Step 6: Using GROUP BY with Multiple Columns

Group employees by department and experience levels to calculate the average salary for each combination:

SELECT department, experience, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, experience;

This query provides more granular insights by grouping by both department and experience.

Step 7: Filtering Groups with HAVING

Use the HAVING clause to filter groups. For example, show departments with a total salary exceeding $150,000:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000;

The HAVING clause filters groups based on aggregate functions like SUM.

Step 8: Combining GROUP BY with ORDER BY

Sort the grouped results. For example, order the departments by their total salary in descending order:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

This query first groups the rows and then sorts the groups based on the aggregate value.

Refer: PostgreSQL – Order By

Conclusion

The GROUP BY clause is a powerful tool in PostgreSQL for summarizing data. You can combine it with aggregate functions, HAVING, and ORDER BY to extract meaningful insights from your data. Experiment with these examples to master its usage.