PostgreSQL GROUP BY: A Guide to Data Aggregation

Aggregating Data in PostgreSQL

Sometimes you don't want individual rows but a summary of them. For example, how many employees the company has or the average salary per person. These are called aggregations. Aggregation is the act of turning many values into one single value. In SQL, aggregate functions take several rows and return one aggregated value.

The main aggregate functions are:

• count – counts all the rows
• sum – adds values together
• avg – averages values
• max and min – find the extremes

Setting Up the Environment

Start the PostgreSQL service and connect to the database:

sudo systemctl status postgresql
sudo -u postgres psql -d company

The database has an employees table with departments, salaries, and people.

Basic Aggregate Functions

Count

Count every row in the employees table:

SELECT count(*) FROM employees;

This returns a single column named count with the value 11, matching the number of rows in the table.

Sum

Sum all salaries:

SELECT sum(salary) FROM employees;

The result is 668000.

Average, Max, and Min with Aliases

When using aggregate functions, it's common to alias the result columns:

SELECT avg(salary) AS average_salary,
       min(salary) AS min_salary,
       max(salary) AS max_salary
FROM employees;

The average salary may have many decimal places. To control the number of digits after the decimal point, wrap the average in the round function:

SELECT round(avg(salary), 2) AS average_salary,
       min(salary) AS min_salary,
       max(salary) AS max_salary
FROM employees;

The second argument to round specifies the number of digits after the decimal. While it's best practice to round all aggregate results, for min and max it won't change anything in this case.

Grouping Data with GROUP BY

The previous queries mix all departments together. To split results by department, use GROUP BY:

SELECT department, count(*) AS people
FROM employees
GROUP BY department;

This shows how many people are in each department.

To see average salaries per department:

SELECT department, avg(salary)
FROM employees
GROUP BY department;

Filtering Groups with HAVING

WHERE cannot be used with aggregate functions. Instead, use HAVING after GROUP BY to filter groups based on aggregated values.

To get departments where the average salary is greater than 55,000:

SELECT department, avg(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING avg_sal > 55000;

If you alias the aggregate column, you can use that alias in the HAVING clause because it executes after the grouping is done.

Why WHERE Doesn't Work

Trying to use WHERE with an aggregate function produces an error:

SELECT department, avg(salary)
FROM employees
WHERE avg(salary) > 55000
GROUP BY department;

This fails with: "aggregate functions are not allowed in WHERE". Aggregations happen after WHERE is evaluated, so WHERE cannot reference them.