Used when you want to perform calculations for each department.

Simple rule:

DISTINCT → Give me unique values

GROUP BY → Give me groups so I can calculate something

🔹 Real-World Data Science Example

Imagine an e-commerce dataset.

You want to know the total revenue generated by each product category.

SELECT category,
       SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

This gives you a category-level summary that can be used for:

• Revenue analysis

• Product performance

• Business reporting

• Feature creation

• Exploratory data analysis

🔹 Common Mistake

Consider:

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

This can cause an error in many SQL databases because employee_name is neither:

• included in GROUP BY, nor

• used inside an aggregate function.

A valid query would be:

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

🔹 Important Rule

When using GROUP BY, selected columns generally need to be either:

1. Included in the GROUP BY, or

2. Wrapped inside an aggregate function.

Example:

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

department → grouped column

COUNT(*) → aggregate

🔹 Interview Questions

1. What is GROUP BY?

GROUP BY groups rows with the same values so aggregate calculations can be performed for each group.

2. Which aggregate functions are commonly used with GROUP BY?

COUNT(), SUM(), AVG(), MIN(), and MAX().

3. What is the difference between WHERE and GROUP BY?

WHERE filters individual rows before grouping.

GROUP BY creates groups from the filtered rows.

4. Can GROUP BY use multiple columns?

Yes.

GROUP BY department, job_title;

5. Can GROUP BY be used without aggregate functions?

Yes, although DISTINCT is often clearer when the goal is simply to return unique combinations.

🎯 Practice Questions

1. Find the number of employees in each department.

2. Find the average salary for each department.

3. Find the total sales for each region.

4. Find the highest salary in each department.

5. Find the total revenue for each product category and sort categories from highest to lowest revenue.

🎯 Key Takeaways

• GROUP BY creates groups of similar rows.

• It is commonly used with aggregate functions.

• COUNT() counts.

• SUM() totals.

• AVG() calculates averages.

• MIN() finds the minimum.

• MAX() finds the maximum.

• WHERE filters rows before grouping.

• ORDER BY can sort grouped results.

• GROUP BY is one of the most important SQL concepts for Data Science and Analytics.

📍Double Tap ❤️ For More