SQL HAVING CLAUSE

SQL HAVING clause specifies that a SQL select  statement should only return rows where aggregate values meet the specified conditions.

The HAVING clause filters the data on the group row but not on the individual row.

It is always used with GROUP BY clause.

SYNTAX

SELECT COLUMN_1, COLUMN_2, .., AGGREGATE_FUNCTION (COLUMN_NAME)  
FROM TABLE_NAME [WHERE CONDITION] 
GROUP BY COLUMN_1, COLUMN_2, .., 
HAVING  [ conditions ]
ORDER BY ASC|DESC

The  COLUMN_1, COLUMN_2, .., are not summarized within an aggregate function and must be included in the GROUP BY clause.

AGGREGATE_FUNCTION can be any SQL valid function such as COUNT,SUM,MIN,MAX, or AVG .

[WHERE CONDITION] is a WHERE CLAUSE that is used to specify any condition. It is an optional.

HAVING[ conditions ] is a condition that applied only to the aggregated results to restrict the groups of returned rows.

ORDER BY is used to sort the result either in ascending or descending order.

Lets look at an example of SQL Having clause.

First, create a sample table named ITEM_DETAILS  as given below.

CREATE TABLE dbo.ITEM_DETAILS (
ID INT NOT NULL, 
ITEM VARCHAR(50), 
ITEM_QTY INT, 
ITEM_PURCHASED_DATE DATETIME)

INSERT INTO dbo.ITEM_DETAILS (ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE)
VALUES (1, 'Scanner', 300, '2019-12-01 00:00:00.000'),
(2, 'Scanner', 250, '2019-12-10 00:00:00.000'),
(3, 'Scanner', 110, '2019-11-12 00:00:00.000'),
(4, 'Scanner', 110, '2019-09-19 00:00:00.000'),
(5, 'Scanner', 280, '2019-09-25 00:00:00.000'),
(6, 'Scanner', 180, '2019-08-15 00:00:00.000'),
(7, 'Scanner', 180, '2019-07-08 00:00:00.000'),
(8, 'Pen Drive', 360, '2019-12-15 00:00:00.000'),
(9, 'Pen Drive', 270, '2019-09-11 00:00:00.000'),
(10, 'Pen Drive', 210 ,'2019-08-22 00:00:00.000'),
(11, 'Pen Drive', 190 ,'2019-08-19 00:00:00.000'),
(12, 'Pen Drive', 480 ,'2019-07-15 00:00:00.000'),
(13, 'Pen Drive', 680, '2019-06-25 00:00:00.000'),
(14, 'Pen Drive', 120, '2019-06-18 00:00:00.000'),
(15, 'Hard Disk', 270, '2019-09-19 00:00:00.000'),
(16, 'Hard Disk', 210, '2019-08-12 00:00:00.000'),
(17, 'Hard Disk', 190, '2019-08-29 00:00:00.000'),
(18, 'Hard Disk', 480, '2019-07-25 00:00:00.000'),
(19, 'Hard Disk', 680, '2019-06-15 00:00:00.000'),
(20, 'Hard Disk', 120, '2019-06-28 00:00:00.000')

Using Having Clause with SUM function

To display only those items for which total sum of purchased quantity is greater than 1500.

Following statement, returns only those items for which total sum of purchased quantity is greater than 1500.

SELECT ITEM , SUM(ITEM_QTY) AS TOTAL_QTY 
FROM dbo.ITEM_DETAILS 
GROUP BY Item
HAVING SUM(ITEM_QTY) > 1500

As you can see, it returns two items for which total purchased quantity is greater than 1500.

Lets understand the above statement by breaking down it into two portion.

First portion: group by clause aggregates the total purchased quantity for each individual item.

Following T-SQL code, returns the total quantity purchased for each item using GROUP BY clause

SELECT ITEM , SUM(ITEM_QTY) AS TOTAL_QTY 
FROM dbo.ITEM_DETAILS 
GROUP BY Item

Second portion: After that, Having clause filters the aggregated result of GROUP BY clause to check the specified condition and returns only those items for which the total purchased quantity is greater than 1500.

SELECT ITEM , SUM(ITEM_QTY) AS TOTAL_QTY 
FROM dbo.ITEM_DETAILS 
GROUP BY Item

SELECT ITEM , SUM(ITEM_QTY) AS TOTAL_QTY 
FROM dbo.ITEM_DETAILS 
GROUP BY Item
HAVING SUM(ITEM_QTY) >1500

Using Having Clause with Count function

To display the total purchased quantity by item for which total purchased count is greater than 6.

Following statement returns total purchased quantity by item for which total purchased count is greater than 6.

SELECT ITEM, SUM(ITEM_QTY) AS TOTAL_QTY, 
COUNT(ITEM_QTY) AS TOTAL_QTY_CNT
FROM dbo.ITEM_DETAILS 
GROUP BY Item
HAVING COUNT(ITEM_QTY)>6

As you can see, it returns all items for which total purchased count that is shown in TOTAL_QTY_CNT column is greater than 6.

Using Order by clause with Having clause

Lets modify the first query which displays items for which total sum of purchased quantity is greater than 1500.

In this statement, we will sort the result by Item Name in descending order.

SELECT ITEM, SUM(ITEM_QTY) AS TOTAL_QTY
FROM dbo.ITEM_DETAILS
GROUP BY Item
HAVING SUM(ITEM_QTY) > 1500
ORDER BY ITEM DESC

Also Read..

GROUP BY CLAUSE