PostgreSQL GROUP BY Clause

PostgreSQL GROUP BY Clause

The GROUP BY clause in PostgreSQL is used to group rows that have the same values in one or more columns and apply aggregate functions like COUNT, SUM, AVG, MAX, and MIN.

1️⃣ Why GROUP BY?

Suppose you have an orders table:

id user_id amount status
1 1 500 completed
2 1 700 completed
3 2 300 pending

To calculate total amount per user, we use GROUP BY.

2️⃣ Basic GROUP BY Syntax

SELECT column_name, aggregate_function(column)
FROM table_name
GROUP BY column_name;

3️⃣ Simple GROUP BY Example

SELECT user_id, COUNT(*) AS total_orders
FROM orders
GROUP BY user_id;

✔ Groups rows by user_id.

4️⃣ GROUP BY with SUM

SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

5️⃣ GROUP BY with Multiple Columns

SELECT user_id, status, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id, status;

✔ Creates groups for each combination of columns.

6️⃣ GROUP BY with HAVING

HAVING filters groups, not rows.

SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

7️⃣ GROUP BY with WHERE

SELECT user_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

✔ WHERE filters rows before grouping
✔ HAVING filters after grouping

8️⃣ GROUP BY with JOIN

SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

9️⃣ Common Aggregate Functions

Function Description
COUNT(*) Total rows
SUM() Total value
AVG() Average
MAX() Highest
MIN() Lowest

🔟 Rules of GROUP BY

✔ Every selected column must be in GROUP BY or be an aggregate
✔ Aliases cannot be used in GROUP BY (use column name)
✔ GROUP BY comes after WHERE and before HAVING

1️⃣1️⃣ Common Errors & Fixes

❌ column must appear in GROUP BY clause
✔ Add column to GROUP BY or aggregate it

❌ using HAVING without GROUP BY
✔ Use WHERE instead

1️⃣2️⃣ Best Practices

✔ Use WHERE before GROUP BY for performance
✔ Index grouped columns on large tables
✔ Use HAVING only for aggregated conditions
✔ Keep GROUP BY minimal

You may also like...