In a database, we often need to organize records into groups and calculate information for each group.
For example, a university may want to know:
PostgreSQL provides the GROUP BY clause to group records based on one or more columns.
We commonly use GROUP BY with aggregate functions such as:
COUNT()SUM()AVG()MIN()MAX()GROUP BY is an SQL clause that groups rows having the same values in one or more columns.
Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
Example:
Suppose we have five students from different cities. We want to count how many students belong to each city.
Instead of counting each city separately, we can use GROUP BY.
Let’s create a table named students.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
age INTEGER,
city VARCHAR(50),
fee NUMERIC(10, 2)
);
Insert sample records:
INSERT INTO students
(student_id, student_name, age, city, fee)
VALUES
(1, 'Ali', 20, 'Multan', 15000),
(2, 'Ahmed', 22, 'DG Khan', 18000),
(3, 'Sara', 21, 'Multan', 20000),
(4, 'Ayesha', 23, 'Lahore', 17000),
(5, 'Usman', 20, 'Lahore', 16000),
(6, 'Hassan', 22, 'DG Khan', 19000),
(7, 'Fatima', 21, 'Multan', 18000);
View all records:
SELECT * FROM students;
We want to find the number of students from each city.
SQL Query:
SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city;
Output:
| city | total_students |
|---|---|
| DG Khan | 2 |
| Lahore | 2 |
| Multan | 3 |
Explanation:
SELECT city displays the city name.COUNT(*) counts students.GROUP BY city creates a separate group for each city.We want to calculate the total fee collected from students in each city.
SQL Query:
SELECT
city,
SUM(fee) AS total_fee
FROM students
GROUP BY city;
Output:
| city | total_fee |
|---|---|
| DG Khan | 37000.00 |
| Lahore | 33000.00 |
| Multan | 53000.00 |
Explanation:
SUM(fee) calculates the total fee.GROUP BY city calculates the total separately for each city.We can calculate the average age of students in each city.
SELECT
city,
AVG(age) AS average_age
FROM students
GROUP BY city;
Output:
| city | average_age |
|---|---|
| DG Khan | 22.0 |
| Lahore | 21.5 |
| Multan | 20.67 |
The average is calculated separately for each city.
We can use MIN() and MAX() with GROUP BY.
Example 1: Minimum fee in each city
SELECT
city,
MIN(fee) AS minimum_fee
FROM students
GROUP BY city;
Example 2: Maximum fee in each city
SELECT
city,
MAX(fee) AS maximum_fee
FROM students
GROUP BY city;
These queries display the smallest and largest fee in each city.
We can use multiple aggregate functions in a single query.
Example: Display student statistics for each city
SELECT
city,
COUNT(*) AS total_students,
SUM(fee) AS total_fee,
AVG(fee) AS average_fee,
MIN(fee) AS minimum_fee,
MAX(fee) AS maximum_fee
FROM students
GROUP BY city;
This query displays:
The WHERE clause filters records before they are grouped.
Example: Count students city-wise whose age is greater than 20.
SELECT
city,
COUNT(*) AS total_students
FROM students
WHERE age > 20
GROUP BY city;
Explanation:
WHERE age > 20 selects students older than 20.GROUP BY city groups the selected students by city.COUNT(*) counts students in each group.We can group records using more than one column.
Example: Group students by city and age.
SELECT
city,
age,
COUNT(*) AS total_students
FROM students
GROUP BY city, age;
In this query, students are grouped according to both their city and age.
For example, students from Multan who are 21 years old will belong to one group.
We can sort grouped results using ORDER BY.
Example: Display cities according to the highest number of students first.
SELECT
city,
COUNT(*) AS total_students
FROM students
GROUP BY city
ORDER BY total_students DESC;
Explanation:
GROUP BY city groups students by city.COUNT(*) counts students in each city.ORDER BY total_students DESC displays the highest count first.Use ASC for ascending order and DESC for descending order.
| WHERE | GROUP BY |
|---|---|
| Filters individual records. | Groups records with common values. |
| Works before grouping. | Creates groups for aggregate calculations. |
| Used to select specific rows. | Used to calculate results for each group. |
Example:
SELECT city, COUNT(*)
FROM students
WHERE age > 20
GROUP BY city;
Here, WHERE filters students first, and GROUP BY groups the remaining students by city.
Remember these rules:
GROUP BY is commonly used with aggregate functions.GROUP BY clause.WHERE filters rows before grouping.ORDER BY sorts the final grouped results.Use the students table to solve the following exercises.
Beginner Level
Intermediate Level
Advanced Level
COUNT(*) calculate within each group?GROUP BY city?| Requirement | SQL Example |
|---|---|
| Count students city-wise | COUNT(*) |
| Total fee city-wise | SUM(fee) |
| Average age city-wise | AVG(age) |
| Minimum fee city-wise | MIN(fee) |
| Maximum fee city-wise | MAX(fee) |
Basic GROUP BY pattern:
SELECT
column_name,
COUNT(*) AS total_records
FROM table_name
GROUP BY column_name;
HAVING Clause in PostgreSQL
Learn how to filter grouped results, such as displaying only those cities that have more than two students or whose total fee exceeds a specified amount.