🚀 Data Science Roadmap 2026

📍 Phase 3: SQL for Data Science

📖 Topic 6 — GROUP BY

GROUP BY is used to group rows that have the same value in one or more columns.

It is mainly used when you want to perform calculations for each group.

For example:

• Total sales by region

• Average salary by department

• Number of customers by city

• Total orders by product

• Maximum transaction by category

🔹 Basic Syntax

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

Common aggregate functions:

• COUNT() → Count rows

• SUM() → Calculate total

• AVG() → Calculate average

• MIN() → Find minimum

• MAX() → Find maximum

🔹 Simple Example

Suppose we have:

department   salary
IT           60000
IT           80000
HR           50000
HR           70000
Finance      90000

Query:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Result:

department   avg_salary
IT           70000
HR           60000
Finance      90000

Instead of calculating one overall average, SQL calculates an average for each department.

🔹 GROUP BY with COUNT()

To count employees in each department:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Result:

department   employee_count
IT           2
HR           2
Finance      1

🔹 GROUP BY with SUM()

Suppose you have sales data:

region   sales
North    10000
North    15000
South    20000
South    12000

Query:

SELECT region, SUM(sales) AS total_sales
FROM sales
GROUP BY region;

Result:

North   25000
South   32000

This is extremely common in business analytics.

🔹 GROUP BY with MAX()

Find the highest salary in each department:

SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

🔹 GROUP BY with MIN()

Find the lowest salary in each department:

SELECT department, MIN(salary) AS lowest_salary
FROM employees
GROUP BY department;

🔹 GROUP BY with Multiple Columns

You can group by multiple columns.

SELECT department, job_title, COUNT(*) AS employee_count
FROM employees
GROUP BY department, job_title;

Here SQL creates groups based on:

department + job_title

For example:

IT        Analyst       5
IT        Developer     8
HR        Analyst       3
Finance   Manager       4

🔹 GROUP BY with WHERE

WHERE filters rows before grouping.

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000
GROUP BY department;

🔹 GROUP BY with ORDER BY

You can sort grouped results.

For example, find departments with the highest total salary first:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

This is very useful for ranking groups.

🔹 GROUP BY vs DISTINCT

These two can sometimes look similar.

DISTINCT

SELECT DISTINCT department
FROM employees;

Used to find unique departments.

GROUP BY

SELECT department, COUNT(*)
FROM employees
GROUP BY department;