HAVING Clause

The HAVING Function

The HAVING clause is used to filter grouped results (GROUP BY) based on specific conditions. Without HAVING, we can only filter individual rows with WHERE.

WHERE vs HAVING

Basic Syntax

SELECT column, aggregate_function(column)
FROM table_name
GROUP BY column
HAVING kondisi;

Execution Order

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

1. WHERE filters rows before grouping
2. GROUP BY groups the remaining rows
3. HAVING filters groups based on aggregate conditions

Example: Departments with More Than 3 Employees

SELECT DEPARTMENT, COUNT(*) AS TOTAL
FROM EMPLOYEE
GROUP BY DEPARTMENT
HAVING COUNT(*) > 3;

Example: Average Salary > 5,000,000

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

Example: Total Salary per Department

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

Combining WHERE and HAVING

-- Filter active employees first, then find departments with more than 3 active employees
SELECT DEPARTMENT, COUNT(*) AS TOTAL_ACTIVE
FROM EMPLOYEE
WHERE STATUS = 'Active'
GROUP BY DEPARTMENT
HAVING COUNT(*) > 3;

Combining with JOIN

SELECT D.DEPARTMENT_NAME, COUNT(*) AS TOTAL, AVG(K.SALARY) AS AVG_SALARY
FROM EMPLOYEE K
INNER JOIN DEPARTMENT D ON K.ID_DEPT = D.ID_DEPT
WHERE K.STATUS = 'Active'
GROUP BY D.DEPARTMENT_NAME
HAVING AVG(K.SALARY) > 5000000
ORDER BY AVG_SALARY DESC;

HAVING with Multiple Conditions

SELECT DEPARTMENT, COUNT(*) AS TOTAL, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEE
GROUP BY DEPARTMENT
HAVING COUNT(*) > 2
   AND AVG(SALARY) > 5000000;

Complete Analysis Example

The EMPLOYEE table:

-- Departments with more than 2 active employees and an average salary above 5 million
SELECT DEPARTMENT,
       COUNT(*) AS ACTIVE_EMPLOYEES,
       ROUND(AVG(SALARY), 0) AS AVG_SALARY
FROM EMPLOYEE
WHERE STATUS = 'Active'
GROUP BY DEPARTMENT
HAVING COUNT(*) > 2
   AND AVG(SALARY) > 5000000;

Result:

HR does not appear because its average salary is below 5 million (after filtering out inactive employees).

Common Mistakes

-- ERROR: WHERE cannot use aggregate functions
SELECT DEPARTMENT, COUNT(*)
FROM EMPLOYEE
WHERE COUNT(*) > 3  -- Wrong!
GROUP BY DEPARTMENT;

-- Correct: use HAVING
SELECT DEPARTMENT, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPARTMENT
HAVING COUNT(*) > 3;

Summary

• WHERE filters individual rows before grouping
• HAVING filters groups after grouping
• HAVING can use aggregate functions (COUNT, AVG, SUM, etc.)
• Combine WHERE and HAVING for flexible data analysis

By mastering HAVING, we have completed the foundation of SQL queries. Combine all the techniques — SELECT, WHERE, JOIN, GROUP BY, and HAVING — for powerful data analysis.