MySQL HAVING Clause

The MySQL HAVING clause is used to select groups by giving conditions using aggregate function with operators. If you want to use HAVING clause, GROUP BY is mandatory because HAVING clause will use aggregate function and tells GROUP BY which groups to include in the output. But when you are using GROUP BY, HAVING is optional. HAVING is to GROUP BY as WHERE is to SELECT. In other words, the WHERE clause places conditions on the selected columns, and the HAVING clause places conditions on groups created by the GROUP BY clause. Therefore, when you use the HAVING clause, you are effectively including or excluding, as the case might be, whole groups of data from the query results.

MySQL HAVING Clause syntax

SELECT COLUMN1, COLUMN2

FROM TABLE

WHERE <Condition>

GROUP BY COLUMN1, COLUMN2

HAVING <ConditionUsingAggreagteFunction>

MySQL HAVING Clause example

Creating table for demonstrating GROUP BY clause

CREATE TABLE EMP

(EMPNO NUMERIC(4) ,

ENAME VARCHAR(10),

JOB VARCHAR(9),

MGR NUMERIC(4),

HIREDATE DATE,

SAL NUMERIC(7,2),

CONSTRAINT PK_EMP PRIMARY KEY(EMPNO)

);

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-4-19',3000);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-5-23',1100);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300);

COMMIT;

Example 1

SELECT SUM(SAL),JOB

FROM EMP

GROUP BY JOB

HAVING SUM(SAL)>5000

Example 2