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 we create a sample table – 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 item for which total sum of purchased quantity is greater than 1500.
Following statement, returns only those item 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 part of T-SQL Code.
First : Group By Clause aggreagtes 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: 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 total purchasead 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 Item for which total purchased count that is shown in TOTAL_QTY_CNT column is greater than 6 for all items.
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 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
Recommended for you
1,577 total views, 2 views today