When working with PostgreSQL, you often need to summarize data instead of returning every individual row.
For example, instead of displaying every order:
Order 1 → $100
Order 2 → $250
Order 3 → $150
Order 4 → $300
you might want to know:
Total orders: 4
Total revenue: $800
Or perhaps:
Revenue by customer
Revenue by country
Orders by status
Average salary by department
PostgreSQL provides GROUP BY and HAVING to make these types of queries possible.
The basic idea is:
GROUP BY
→ Groups rows
HAVING
→ Filters groups
They are commonly used together with aggregate functions such as:
COUNT()
SUM()
AVG()
MIN()
MAX()
In this guide, we’ll learn how GROUP BY and HAVING work in PostgreSQL, understand the difference between WHERE and HAVING, and explore practical examples.
What Is GROUP BY?
GROUP BY combines rows that have the same values in one or more columns into groups.
For example, suppose you have an orders table:
id | customer | status
---|----------|----------
1 | Oliver | completed
2 | Sophia | pending
3 | Oliver | completed
4 | Ethan | shipped
5 | Sophia | completed
You can group orders by status:
SELECT status
FROM orders
GROUP BY status;
The result might be:
completed
pending
shipped
Each unique status becomes a group.
GROUP BY With COUNT()
GROUP BY becomes much more useful when combined with aggregate functions.
For example:
SELECT
status,
COUNT(*) AS total_orders
FROM orders
GROUP BY status;
Result:
status | total_orders
-------------|-------------
completed | 3
pending | 1
shipped | 1
Here:
GROUP BY status
↓
Creates groups
↓
COUNT(*)
↓
Counts rows in each group
What Are Aggregate Functions?
Aggregate functions calculate a single result from multiple rows.
Common PostgreSQL aggregate functions include:
| Function | Purpose |
|---|---|
COUNT() | Counts rows |
SUM() | Calculates total |
AVG() | Calculates average |
MIN() | Finds minimum |
MAX() | Finds maximum |
For example:
SELECT COUNT(*)
FROM orders;
returns the total number of orders.
GROUP BY With SUM()
Suppose your orders table contains:
id | customer | amount
---|----------|-------
1 | Oliver | 100
2 | Sophia | 200
3 | Oliver | 150
4 | Ethan | 300
5 | Sophia | 250
You can calculate total spending per customer:
SELECT
customer,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer;
Result:
customer | total_spent
---------|------------
Oliver | 250
Sophia | 450
Ethan | 300
GROUP BY With AVG()
You can calculate an average.
Suppose you have employees:
id | department | salary
---|------------|-------
1 | IT | 50000
2 | IT | 60000
3 | HR | 45000
4 | HR | 55000
Query:
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
Result:
department | average_salary
-----------|---------------
IT | 55000
HR | 50000
GROUP BY With MIN() and MAX()
You can find the lowest and highest value within each group.
SELECT
department,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;
Result:
department | minimum_salary | maximum_salary
-----------|----------------|---------------
IT | 50000 | 60000
HR | 45000 | 55000
GROUP BY Multiple Columns
You can group by more than one column.
For example:
SELECT
country,
status,
COUNT(*) AS total_orders
FROM orders
GROUP BY country, status;
PostgreSQL first groups rows by the combination:
country + status
For example:
country | status | total_orders
--------|-------------|-------------
India | completed | 100
India | pending | 20
USA | completed | 80
USA | pending | 15
Why Multiple GROUP BY Columns Matter
Suppose you only use:
GROUP BY country
you get:
India → 120
USA → 95
But:
GROUP BY country, status
gives more detailed information:
India → completed → 100
India → pending → 20
USA → completed → 80
USA → pending → 15
This is useful for reporting and analytics.
GROUP BY With Expressions
You can group using expressions as well.
For example:
SELECT
EXTRACT(YEAR FROM created_at) AS year,
COUNT(*) AS total_orders
FROM orders
GROUP BY EXTRACT(YEAR FROM created_at);
This can produce:
year | total_orders
-----|-------------
2024 | 1200
2025 | 1800
2026 | 2400
GROUP BY With Date
You can group records by month.
For example:
SELECT
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS total_orders
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
Result:
month | total_orders
------------|-------------
2026-01-01 | 120
2026-02-01 | 150
2026-03-01 | 180
This is a common pattern for dashboards and reports.
What Is HAVING?
HAVING filters groups created by GROUP BY.
For example:
SELECT
customer,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer
HAVING COUNT(*) > 5;
This means:
GROUP BY customer
↓
Count orders
↓
Keep only customers
with more than 5 orders
HAVING Example
Suppose:
customer | orders
---------|-------
Oliver | 10
Sophia | 7
Ethan | 3
Emma | 1
Query:
SELECT
customer,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer
HAVING COUNT(*) > 5;
Result:
Oliver → 10
Sophia → 7
Ethan and Emma are removed because their groups don’t satisfy the condition.
WHERE vs HAVING
This is one of the most important concepts.
WHERE
Filters individual rows before grouping.
HAVING
Filters groups after grouping.
Think of it like:
WHERE
↓
Filter rows
↓
GROUP BY
↓
Create groups
↓
HAVING
↓
Filter groups
WHERE Example
Suppose you only want completed orders:
SELECT
customer,
COUNT(*) AS total_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer;
Here:
WHERE status = 'completed'
filters rows first.
Then PostgreSQL groups the remaining rows.
HAVING Example
Suppose you want customers with more than five completed orders:
SELECT
customer,
COUNT(*) AS total_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer
HAVING COUNT(*) > 5;
The execution concept is:
1. WHERE
↓
Keep completed orders
2. GROUP BY
↓
Group by customer
3. COUNT
↓
Count each customer's orders
4. HAVING
↓
Keep customers with > 5
WHERE and HAVING Together
You can use both.
For example:
SELECT
customer,
SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer
HAVING SUM(amount) > 1000;
This means:
Only completed orders
↓
Group by customer
↓
Calculate total spending
↓
Keep customers spending > $1000
This is one of the most common GROUP BY + HAVING patterns.
Can HAVING Be Used Without GROUP BY?
Yes.
PostgreSQL allows HAVING in queries that produce a single aggregate group.
For example:
SELECT
COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;
The entire result is treated as one group.
If there are more than 100 orders, the result is returned.
Otherwise, no row is returned.
GROUP BY and COUNT(DISTINCT)
You can count unique values within each group.
For example:
SELECT
country,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY country;
Result:
country | unique_customers
--------|-----------------
India | 500
USA | 350
UK | 200
This is useful when multiple orders can belong to the same customer.
GROUP BY and SUM(DISTINCT)
PostgreSQL also supports:
SUM(DISTINCT amount)
For example:
SELECT
customer_id,
SUM(DISTINCT amount) AS unique_total
FROM orders
GROUP BY customer_id;
Be careful with DISTINCT inside aggregates because it changes the meaning of the calculation.
GROUP BY With JOIN
GROUP BY is especially powerful when working with related tables.
Suppose:
customers
---------
id
name
orders
------
id
customer_id
amount
You can calculate revenue per customer:
SELECT
customers.name,
SUM(orders.amount) AS total_spent
FROM customers
JOIN orders
ON orders.customer_id = customers.id
GROUP BY customers.id, customers.name;
Result:
name | total_spent
-------|------------
Oliver | 1500
Sophia | 2300
Ethan | 900
GROUP BY With JOIN and HAVING
Now suppose you want customers who spent more than $2,000:
SELECT
customers.name,
SUM(orders.amount) AS total_spent
FROM customers
JOIN orders
ON orders.customer_id = customers.id
GROUP BY customers.id, customers.name
HAVING SUM(orders.amount) > 2000;
This produces only customers whose total spending exceeds $2,000.
GROUP BY and NULL Values
PostgreSQL groups NULL values together.
Suppose:
department
----------
IT
HR
NULL
IT
NULL
Then:
SELECT
department,
COUNT(*)
FROM employees
GROUP BY department;
can produce:
department | count
-----------|------
IT | 2
HR | 1
NULL | 2
All NULL values belong to the same group.
GROUP BY and ORDER BY
You can sort grouped results.
For example:
SELECT
customer,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC;
Result:
customer | total_spent
---------|------------
Sophia | 5000
Oliver | 3500
Ethan | 1200
This is useful for ranking.
Top Customers Example
To find the top five customers:
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;
This pattern is common in dashboards and business reports.
HAVING With Multiple Conditions
You can combine conditions.
For example:
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5
AND SUM(amount) >= 1000;
This keeps customers who:
Have at least 5 orders
AND
Have spent at least $1,000
HAVING With OR
You can also use OR:
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 5000
OR COUNT(*) > 20;
This returns customers satisfying either condition.
HAVING With COUNT
A common use case:
SELECT
category,
COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;
This finds categories containing more than ten products.
HAVING With AVG
For example:
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
This returns only departments whose average salary exceeds $60,000.
HAVING With SUM
For revenue:
SELECT
product_id,
SUM(quantity * price) AS revenue
FROM order_items
GROUP BY product_id
HAVING SUM(quantity * price) > 10000;
This finds products generating more than $10,000 in revenue.
HAVING With MIN and MAX
You can filter based on minimum or maximum values:
SELECT
department,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 100000;
This returns departments containing at least one employee earning more than $100,000.
GROUP BY With CASE
You can create custom groups using CASE.
For example:
SELECT
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age < 30 THEN '18-29'
WHEN age < 50 THEN '30-49'
ELSE '50+'
END AS age_group,
COUNT(*) AS total_users
FROM users
GROUP BY
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age < 30 THEN '18-29'
WHEN age < 50 THEN '30-49'
ELSE '50+'
END;
This allows you to create reporting categories dynamically.
GROUP BY and Aggregate Aliases
A common question is whether you can use an aggregate alias in HAVING.
For example:
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000;
In PostgreSQL, this is not the standard way to reference the SELECT-list alias in HAVING.
Prefer:
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;
This is clearer and reliable.
GROUP BY Rules in PostgreSQL
When using GROUP BY, every selected expression generally needs to be either:
An aggregate
or:
Included in GROUP BY
For example:
SELECT
customer_id,
customer_name,
COUNT(*)
FROM customers
GROUP BY customer_id, customer_name;
Both non-aggregated columns are included in the grouping.
Invalid GROUP BY Example
This can be problematic:
SELECT
customer_id,
customer_name,
COUNT(*)
FROM customers
GROUP BY customer_id;
If customer_name isn’t functionally determined by the grouped columns in a way PostgreSQL can establish, PostgreSQL will reject the query.
A safe approach is to group by all selected non-aggregate columns:
GROUP BY customer_id, customer_name;
GROUP BY With Boolean Values
You can group by a Boolean column:
SELECT
is_active,
COUNT(*) AS total_users
FROM users
GROUP BY is_active;
Result:
is_active | total_users
----------|------------
true | 900
false | 100
GROUP BY With Text
You can group text values:
SELECT
role,
COUNT(*) AS total_users
FROM users
GROUP BY role;
Result:
role | total_users
--------|------------
admin | 10
user | 900
manager | 50
GROUP BY With JSON Data
PostgreSQL also allows grouping by expressions extracted from JSON/JSONB data.
For example:
SELECT
metadata->>'country' AS country,
COUNT(*) AS total_users
FROM users
GROUP BY metadata->>'country';
This can be useful when working with semi-structured data.
However, if you frequently query a field, consider whether that field should be stored as a regular column or indexed appropriately.
GROUP BY With JSONB
For JSONB data:
SELECT
metadata->>'plan' AS plan,
COUNT(*) AS total_users
FROM users
GROUP BY metadata->>'plan';
You might get:
plan | total_users
-------|------------
free | 1000
pro | 300
team | 50
GROUP BY for Analytics
GROUP BY is one of the most important SQL features for analytics.
You can calculate:
Revenue by month
Orders by country
Users by subscription
Sales by product
Tickets by priority
Employees by department
For example:
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
GROUP BY for Dashboards
A dashboard might need:
Total users
Active users
Orders today
Revenue this month
Top products
Top customers
PostgreSQL can calculate many of these values using aggregate functions and GROUP BY.
For example:
SELECT
status,
COUNT(*) AS total
FROM orders
GROUP BY status;
The backend can then send the grouped results to the frontend.
GROUP BY and Performance
GROUP BY can require PostgreSQL to process a large number of rows.
Performance depends on:
- Number of rows
- Number of groups
- Aggregate functions
- Filtering
- Indexes
- Available memory
- Query plan
- Table structure
Use EXPLAIN and EXPLAIN ANALYZE when investigating performance.
For example:
EXPLAIN ANALYZE
SELECT
customer_id,
SUM(amount)
FROM orders
GROUP BY customer_id;
This helps you understand how PostgreSQL executes the query.
Filter Before Grouping When Possible
Suppose you only need completed orders.
Prefer:
SELECT
customer_id,
SUM(amount)
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;
rather than grouping all rows and trying to filter them afterward.
The reason is simple:
WHERE
→ Reduces rows before grouping
This can reduce the amount of data PostgreSQL needs to group.
WHERE vs HAVING: Easy Memory Trick
Remember:
WHERE
→ Filters rows
HAVING
→ Filters groups
For example:
WHERE amount > 100
means:
Only consider rows where amount is greater than 100.
While:
HAVING SUM(amount) > 1000
means:
Keep groups whose total amount is greater than 1000.
Real-World Example
Suppose you have an e-commerce application.
You want to find customers who:
- Have placed at least 5 completed orders.
- Have spent more than $2,000.
Query:
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 5
AND SUM(amount) > 2000
ORDER BY total_spent DESC;
The query follows a logical flow:
Completed orders
↓
Group by customer
↓
Count orders
↓
Calculate total spending
↓
Keep customers with:
5+ orders
AND
$2,000+ spending
↓
Sort by spending
This is a practical example of how WHERE, GROUP BY, HAVING, and ORDER BY work together.
Common Mistakes
Mistake 1: Using WHERE for Aggregate Conditions
Don’t write:
WHERE COUNT(*) > 5
Aggregate conditions belong in:
HAVING COUNT(*) > 5
Mistake 2: Using HAVING for Simple Row Filters
If you want:
status = 'completed'
use:
WHERE status = 'completed'
instead of unnecessarily putting the condition in HAVING.
Mistake 3: Forgetting GROUP BY Columns
If you select a normal column along with an aggregate, make sure the query’s grouping logic supports it.
Mistake 4: Grouping Too Many Columns
For example:
GROUP BY
id,
customer_id,
name,
email,
address,
created_at
may produce groups that are far more granular than intended.
Always group according to the actual reporting requirement.
Mistake 5: Ignoring NULL
Remember that NULL values form a group when grouped by a column.
GROUP BY vs DISTINCT
GROUP BY and DISTINCT can sometimes produce similar-looking results, but they have different purposes.
DISTINCT
Used to remove duplicate result values:
SELECT DISTINCT country
FROM users;
GROUP BY
Used primarily to create groups for aggregation:
SELECT
country,
COUNT(*)
FROM users
GROUP BY country;
A simple rule:
Need unique values?
→ DISTINCT
Need calculations per group?
→ GROUP BY
GROUP BY vs Window Functions
GROUP BY reduces multiple rows into one row per group.
For example:
SELECT
department,
AVG(salary)
FROM employees
GROUP BY department;
Window functions calculate across related rows while preserving individual rows.
For example:
SELECT
name,
department,
salary,
AVG(salary) OVER (
PARTITION BY department
) AS department_average
FROM employees;
Use GROUP BY when you want one result per group.
Use a window function when you need individual rows plus group-level calculations.
Quick Cheat Sheet
Count rows by category
SELECT
category,
COUNT(*)
FROM products
GROUP BY category;
Total sales by customer
SELECT
customer_id,
SUM(amount)
FROM orders
GROUP BY customer_id;
Average salary by department
SELECT
department,
AVG(salary)
FROM employees
GROUP BY department;
Groups with more than 10 rows
SELECT
category,
COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 10;
Filter rows before grouping
SELECT
customer_id,
SUM(amount)
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;
Filter groups after grouping
SELECT
customer_id,
SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;
Final Thoughts
GROUP BY and HAVING are essential PostgreSQL features for reporting, analytics, and data aggregation.
Remember the basic flow:
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
↓
LIMIT
The most important difference is:
GROUP BY
→ Creates groups
HAVING
→ Filters groups
WHERE
→ Filters rows before grouping
For example:
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 5
AND SUM(amount) > 2000
ORDER BY total_spent DESC;
Once you understand the difference between row-level filtering with WHERE and group-level filtering with HAVING, you can build powerful PostgreSQL queries for reports, dashboards, analytics, and business applications.
Use WHERE to filter rows, GROUP BY to create groups, and HAVING to filter those groups.
