SQL HAVING Clause with example

SQL HAVING Clause

• HAVING filters records that work on summarized GROUP BY results.
• HAVING applies to summarized group records, whereas WHERE applies to individual records.
• Only the groups that meet the HAVING criteria will be returned.
• HAVING requires that a GROUP BY clause is present.
• WHERE and HAVING can be in the same query.

The SQL HAVING syntax

The general syntax is:

1. SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition

The general syntax with ORDER BY is:

1. SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY column-names

SQL GROUP BY Examples

1) Example of having Clause  without order by

mysql> select name,count(age) from person group by name having count(age>5);
+-----------------+------------+
| name            | count(age) |
+-----------------+------------+
| chayan das      |          1 |
| jit             |          1 |
| mono            |          1 |
| monojit         |          1 |
| sajal           |          2 |
| shantanu biswas |          1 |
| sourav biswas   |          1 |
| subham ball     |          1 |
| sudip saha      |          1 |
+-----------------+------------+
9 rows in set (0.00 sec)

2) Example of having Clause  with order by

mysql> select name,count(age) from person group by name having count(age>5) order by name;
+-----------------+------------+
| name            | count(age) |
+-----------------+------------+
| chayan das |          1 |
| jit                |          1 |
| mono          |          1 |
| monojit       |          1 |
| sajal            |          2 |
| shantanu biswas |          1 |
| sourav biswas     |          1 |
| subham ball  |          1 |
| sudip saha    |          1 |
+-----------------+------------+
9 rows in set (0.00 sec)