PostgreSQL GROUP BY
Group rows with the same values and perform aggregate calculations
📊 What is GROUP BY?
GROUP BY groups rows that have the same values in specified columns. It's used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to perform calculations on each group.
-- Basic GROUP BY syntax
SELECT column, aggregate_function(column)
FROM table
GROUP BY column;
Key GROUP BY Concepts
Aggregate Functions
Calculate values for each group
SELECT city, COUNT(*)
FROM customers
GROUP BY city;Group Rows
Combine rows with same values
SELECT category, SUM(price)
FROM products
GROUP BY category;Multiple Columns
Group by multiple columns
SELECT city, country, COUNT(*)
FROM customers
GROUP BY city, country;Summary Reports
Create statistical summaries
SELECT department, AVG(salary)
FROM employees
GROUP BY department;🔹 Basic GROUP BY Example
Count how many customers are in each city. GROUP BY combines all customers from the same city into one row, and COUNT calculates the total for each group.
-- Sample table
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(50)
);
-- Insert sample data
INSERT INTO customers (name, city) VALUES
('John', 'Boston'),
('Jane', 'Boston'),
('Mike', 'Seattle'),
('Sarah', 'Boston');
-- GROUP BY query
SELECT
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC;Output:
| city | customer_count |
|---|---|
| Boston | 3 |
| Seattle | 1 |
🔹 GROUP BY with SUM and AVG
Calculate total sales and average order value per customer. GROUP BY organizes orders by customer, then aggregate functions compute totals and averages for each customer's orders.
-- Calculate sales statistics per customer
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(amount) AS total_sales,
AVG(amount) AS average_order
FROM orders
GROUP BY customer_id
ORDER BY total_sales DESC;Output:
| customer_id | total_orders | total_sales | average_order |
|---|---|---|---|
| 1 | 3 | 450.00 | 150.00 |
| 2 | 2 | 300.00 | 150.00 |
🔹 GROUP BY Multiple Columns
Group by both city and country to get more detailed breakdowns. Multiple column grouping creates separate groups for each unique combination of the specified column values.
-- Group by city and country
SELECT
country,
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY country, city
ORDER BY country, city;Output:
| country | city | customer_count |
|---|---|---|
| USA | Boston | 3 |
| USA | Seattle | 2 |
| Canada | Toronto | 1 |
🔹 GROUP BY with WHERE Clause
Filter rows before grouping using WHERE clause. WHERE filters individual rows first, then GROUP BY organizes the filtered results and applies aggregate functions to each group.
-- Filter before grouping
SELECT
category,
COUNT(*) AS product_count,
AVG(price) AS avg_price
FROM products
WHERE price > 50
GROUP BY category
ORDER BY avg_price DESC;Output:
| category | product_count | avg_price |
|---|---|---|
| Electronics | 5 | 299.99 |
| Furniture | 3 | 199.99 |
🔹 Common Aggregate Functions
PostgreSQL provides several aggregate functions for GROUP BY. Each function performs different calculations on grouped data, from counting rows to finding minimum and maximum values.
-- Using multiple aggregate functions
SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;Output:
| department | employee_count | total_salary | avg_salary | min_salary | max_salary |
|---|---|---|---|---|---|
| Sales | 4 | 240000 | 60000 | 50000 | 75000 |