SQL HAVING |
SQL Sample Code - SQL HAVING. |
| SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name(s) HAVING aggregate_function(column_name) operator value; |
See the SQL samples section below for an explanation of SQL HAVING.
What does SQL HAVING do? |
The SQL HAVING clause is similar to the WHERE clause. The WHERE clause cannot be used when working with Aggregate Functions. The HAVING clause is used to filter out records based upon criteria stipulated in the HAVING section of the select statement.
In a WHERE clause, the search condition performed on rows is done so before the rows are grouped. In a HAVING clause, the groups are formed first and then the specified HAVING conditions are applied to the group.
Only database columns used in the GROUP BY section may be used for the HAVING clause.
Sample 1 – SQL HAVING |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town | Sales |
| 1 | SQL Sample | 1 Sample St | Hamburg | 10000 |
| 2 | SQL Code Land | 2 Code Rd | Hamburg | 20000 |
| 3 | Sample Code World | 66 SQL St | Curry | 18000 |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa | 17000 |
| 5 | MySQL Code Geeks | 5 Sample Ave | Las Vegas | 21000 |
| 6 | SQL Sample House | 32 Code Rd | Hamburg | 15500 |
| 7 | SQL Group Land | 5 Function St | Curry | 18125 |
In this SQL sample we want know whether any particular Town’s Sales figures are greater than 25000.
Use the SELECT statement below:
| SELECT Town,SUM(Sales) FROM tblCompany GROUP BY Town HAVING SUM(Sales)>25000; |
The table of results will look like this:
| Town | SUM(Sales |
| Hamburg | 45500 |
| Curry | 36125 |
Note: To learn more about the SUM() function go to the SUM() section.
Sample 2 – SQL HAVING using WHERE clause. |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town | Sales |
| 1 | SQL Sample | 1 Sample St | Hamburg | 10000 |
| 2 | SQL Code Land | 2 Code Rd | Hamburg | 20000 |
| 3 | Sample Code World | 66 SQL St | Curry | 30000 |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa | 17000 |
| 5 | MySQL Code Geeks | 5 Sample Ave | Las Vegas | 21000 |
| 6 | SQL Sample House | 32 Code Rd | Hamburg | 15500 |
| 7 | SQL Group Land | 5 Function St | Curry | 18125 |
In this SQL sample we want know whether any particular Town’s total Sales figures are greater than or equal to (>=) 30000 not including (< >) Sandwich Land’s figures.
Go to the WHERE section to learn more about WHERE, >= and < >
Use the SELECT statement below:
| SELECT Town,SUM(Sales) FROM tblCompany WHERE CompanyName <>”Sandwich Land” GROUP BY Town HAVING SUM(Sales)>=30000; |
The table of results will look like this:
| Town | SUM(Sales |
| Curry | 30000 |
Note: As the HAVING clause applies the criteria after the WHERE clause has been executed, Hamburg’s SQL Code Land Sales figure of 20000 has been omitted from Hamburg’s SUM(Sales) figure.
Note: To learn more about the WHERE clause go to the WHERE section.
Related SQL Sample Code:
|
|