PostgreSQL Group By
Understanding GROUP BY in PostgreSQL
The GROUP BY instruction helps you cluster rows that share the same values in certain columns. It’s commonly used when you want to summarize, calculate totals, or analyze patterns in grouped records.
🎯 Why Use GROUP BY?
Imagine you have multiple entries for the same city, and you want to find the total sales for each one. Instead of going through each line, GROUP BY lets PostgreSQL gather and combine those rows under one umbrella.
✅ Example: Count Entries Per Category
Let’s say we have a table called orders and we want to count how many orders each customer has placed.
SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id;
This outputs one row per customer, showing how many records are linked to each.
Using GROUP BY with Aggregates
GROUP BY is paired with aggregate functions like:
- COUNT() – number of items
- SUM() – total of values
- AVG() – average value
- MAX() – highest value
- MIN() – lowest value
Another Example: Average Price Per Brand
SELECT brand, AVG(price) AS average_price FROM products GROUP BY brand;
This returns a list of brands and their corresponding average price.
🧠 Things to Remember
All columns in the SELECT that are not aggregated must be included in the GROUP BY.
You can group by more than one column if needed.
Multi-Field Grouping Example
SELECT region, salesperson, SUM(sales) AS total_revenue FROM deals GROUP BY region, salesperson;
Here, results are grouped by both region and individual salesperson, showing the sales total for each combo.
Summary Table
| Feature | Purpose | Notes |
|---|---|---|
| GROUP BY | Collects rows by matching data | Used with functions like SUM(), etc. |
| HAVING | Filters grouped results | Like WHERE, but for groups |
Prefer Learning by Watching?
Watch these YouTube tutorials to understand POSTGRESQL Tutorial visually:
What You'll Learn:
- 📌 Working with Rollups and Cubes in PostgreSQL | SQL Basics
- 📌 GROUP BY and HAVING Clause in SQL