Selection criteria for grouped tables (HAVING) - SQL Strategy - A site to master SQL while executing it on the Web

Learn how to extract conditions from grouped tables.

１．HAVINGの使い方

--Extract JOBs (jobs) with a SAL (salary) average of 2500 or more


SELECT	JOB
FROM	EMP
GROUP BY	JOB
HAVING	AVG(SAL) >= 2500

■ How to use HAVING

HAVING allows the user to set an extraction condition for a GROUP BY clause, whereas a WHERE condition is a condition at the record extraction stage before the records are grouped by GROUP BY.

■ How to describe HAVING

After the GROUP BY clause, [ HAVING Conditional Expression ], the condition used in the HAVING clause must have a value for each group, so it can be a comparison condition for a grouped key or set function.

■ 実習

Now for the Practice, I will give you two questions.

Practice (1/2) Questions

Group by DEPTNO (department No.) from the EMP Table and display the DEPTNO and number of employees with 4 or more employees in the department.

Practice (1/2) Answers

SELECT DEPTNO,COUNT(*)FROM EMP

GROUP BY DEPTNO HAVING COUNT(*) >= 4

The argument to COUNT can be any column name

Practice (2/2) Questions

From the EMP Table, group by JOB (job) and display the JOBs and SALs (min, max, average) with a maximum SAL (salary) value greater than 2000.

Practice (2/2) Answers

SELECT JOB,MIN(SAL),MAX(SAL),AVG(SAL) FROM EMP

GROUP BY JOB HAVING MAX(SAL) >= 2000

2.Selection criteria for grouped tables (HAVING)

Database Specialist AM 2001 Q37

Question
Answer and Explanation

Which SQL statement is appropriate for searching for the same name in the "Employees" Table?

Employees

EmployeeNo	Name	Date of birth	Department
0001	Arai　Kenji	1950-02-04	sales
0002	Suzuki　Taro	1955-03-13	general affairs
0003	Sato　Hiroshi	1961-07-11	Engineering
0004	Tanaka　Hiroshi	1958-01-24	planning
0005	Suzuki　Taro	1948-11-09	sales
・・・

A	SELECT　DISTINCT Name FROM Employees ORDER BY Name
B	SELECT　Name FROM Employees GROUP BY Name HAVING COUNT(*) > 1
C	SELECT　Name FROM Employees WHERE Name > 1
D	SELECT　Name FROM Employees WHERE Name = Name

Answer

B

Explanation

If you are looking for the same family name, you can group by Name, count the number of records, and search for those with 2 or more records.

Conditions on records grouped by GROUP BY are specified by HAVING.

DISTINCT removes duplicate rows at the display stage.

This completes [ Selection criteria for grouped tables (HAVING)].

Conditional extraction of grouped tables (HAVING)

１．HAVINGの使い方

■ How to use HAVING

■ How to describe HAVING

■ 実習

2.Selection criteria for grouped tables (HAVING)