Having
HAVING filters records that work on summarized GROUP BY results.
HAVING must come after GROUP BY.
WHERE and HAVING clause can be in the same query.
Only the groups that meet the HAVING criteria will be returned.
SQL HAVING Syntax:
SELECT Column1
FROM Table_Name
Group BY Column1
Having condition
(Order by Column1 ) -Optional
Syntax with Where clause:
SELECT Column1
FROM Table_Name
WHERE condition
Group BY Column1
Having condition
(Order by Column1 ) -Optional
Example:
Lets use the “Products” table to find the sum by product but we only want to return products with a sum over 1500.
| ProductID | ProductName | Description | Price | SupplierName | SupplierID |
|---|---|---|---|---|---|
| 1 | Super Printer | 9000 series printer | 300 | TechOne | 1 |
| 2 | Laptop 5 | 4000 Laptop 5th generation | 700 | TechTwo | 2 |
| 3 | 3D Printer | 3D Print Pro | 900 | TechOne | 1 |
| 4 | Labtop 7 | 100 Labtop 7 processor | 1200 | TechTwo | 2 |
| 5 | Camera 400 | Camera Ultra 400 | 400 | TechThree | 3 |
SELECT
SUM (Price) as TotalPrice,
SupplierName,
LastName
FROM Products
GROUP BY SupplierName
HAVING SUM (Price) > 1500
Results:
| TotalPrice | SupplierName |
|---|---|
| 1900 | TechTwo |
SQL Tutorial