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

Conditional extraction of grouped tables (HAVING)

Learn how to extract conditions from grouped tables.

1.HAVINGの使い方

--Extract JOBs (jobs) with a SAL (salary) average of 2500 or more
SELECTJOB
FROMEMP
GROUP BYJOB
HAVINGAVG(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.


2.Selection criteria for grouped tables (HAVING)

Database Specialist AM 2001 Q37
Which SQL statement is appropriate for searching for the same name in the "Employees" Table?

Employees
EmployeeNoName Date of birth Department
 0001 Arai Kenji1950-02-04sales
 0002 Suzuki Taro1955-03-13general affairs
 0003 Sato Hiroshi1961-07-11Engineering
 0004 Tanaka Hiroshi1958-01-24planning
 0005 Suzuki Taro1948-11-09sales
・・・

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)].