🔹 GROUP BY Command
Definition:
The GROUP BY statement groups rows that have the same values in specified columns into summary rows, often used with aggregate functions like COUNT(), SUM(), AVG(), etc.
Syntax:
SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;
Example:
Show the total salary department-wise.
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
Sample employees table:
| name | department | salary |
|---|
| Alice | IT | 60000 |
| Bob | HR | 50000 |
| Charlie | IT | 70000 |
| Daisy | HR | 55000 |
| Eve | Finance | 80000 |
🟢 Result:
| department | total_salary |
|---|
| IT | 130000 |
| HR | 105000 |
| Finance | 80000 |
🔍 How the Output Was Calculated:
- All rows were grouped based on the
department column.
- Then,
SUM(salary) was applied within each group.
- IT group had 2 salaries: 60000 and 70000 → Total = 130000.
- HR group had 2 salaries: 50000 and 55000 → Total = 105000.
- Finance group had 1 salary: 80000 → Total = 80000.
That’s how you got one result per department, each with a total salary.
When to Use:
Use GROUP BY when you want to calculate totals, averages, counts, or other statistics for each group of data based on a column.
Common Mistake:
❌ Selecting columns that are not part of the
GROUP BY clause or an aggregate function.
SELECT department, name FROM employees GROUP BY department;
🔴 This will give an error unless your SQL dialect supports it.
🔎 Tip: Always pair GROUP BY with aggregate functions unless all selected columns are grouped.
Real-world Use Case:
Find how many employees are there in each department:
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;