GROUP BY

The GROUP BY Function

The GROUP BY clause is used to group rows that have the same value in a specific column. It is usually combined with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX.

Basic Syntax

SELECT column, aggregate_function(other_column)
FROM table_name
GROUP BY column;

Aggregate Functions

Function	Description
`COUNT(*)`	Counts the number of rows
`SUM(column)`	Sums the values
`AVG(column)`	Calculates the average
`MIN(column)`	Minimum value
`MAX(column)`	Maximum value

Example: Employee Count per Department

SELECT DEPARTMENT, COUNT(*) AS TOTAL_EMPLOYEE
FROM EMPLOYEE
GROUP BY DEPARTMENT;

Result:

DEPARTMENT	TOTAL_EMPLOYEE
IT	8
HR	5
FINANCE	6
MARKETING	4

Example: Average Salary per Department

SELECT DEPARTMENT, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEE
GROUP BY DEPARTMENT;

Example: Total Salary per Department

SELECT DEPARTMENT, SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEE
GROUP BY DEPARTMENT;

MIN and MAX

SELECT DEPARTMENT,
       MIN(SALARY) AS LOWEST_SALARY,
       MAX(SALARY) AS HIGHEST_SALARY
FROM EMPLOYEE
GROUP BY DEPARTMENT;

GROUP BY with Multiple Columns

Groups based on a combination of several columns:

SELECT DEPARTMENT, STATUS, COUNT(*) AS TOTAL
FROM EMPLOYEE
GROUP BY DEPARTMENT, STATUS;

Result:

DEPARTMENT	STATUS	TOTAL
IT	Active	7
IT	Inactive	1
HR	Active	4
HR	Inactive	1

GROUP BY with SUM and AVG

SELECT DEPARTMENT,
       COUNT(*) AS TOTAL,
       SUM(SALARY) AS TOTAL_SALARY,
       AVG(SALARY) AS AVG_SALARY,
       MIN(SALARY) AS MIN_SALARY,
       MAX(SALARY) AS MAX_SALARY
FROM EMPLOYEE
GROUP BY DEPARTMENT;

GROUP BY with JOIN

SELECT D.DEPARTMENT_NAME, COUNT(*) AS TOTAL
FROM EMPLOYEE K
INNER JOIN DEPARTMENT D ON K.ID_DEPT = D.ID_DEPT
GROUP BY D.DEPARTMENT_NAME
ORDER BY TOTAL DESC;

GROUP BY with ORDER BY

-- Sort by the department with the most employees
SELECT DEPARTMENT, COUNT(*) AS TOTAL
FROM EMPLOYEE
GROUP BY DEPARTMENT
ORDER BY TOTAL DESC;

Common Mistakes

-- ERROR: The 'NAME' column is not an aggregate function and is not in GROUP BY
SELECT NAME, DEPARTMENT, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPARTMENT;

Every column in SELECT must be either an aggregate function or included in GROUP BY.

Complete Analysis Example

-- Average salary per department, show only those with an average above 5 million
-- (group filtering is done in the next chapter with HAVING)
SELECT DEPARTMENT,
       COUNT(*) AS TOTAL,
       ROUND(AVG(SALARY), 0) AS AVG_SALARY
FROM EMPLOYEE
GROUP BY DEPARTMENT
ORDER BY AVG_SALARY DESC;

Usage Tips

Always include the GROUP BY column in SELECT so the results are easy to read
Use aliases for aggregate columns for clarity
Sort the results with ORDER BY for better analysis
Be careful with NULL — GROUP BY groups all NULLs into a single group

To filter grouped results based on a specific condition (for example, departments with more than 3 employees), we need the HAVING clause, which is covered in the next chapter.