where clause can not accept aggregate function like sum, count, etc. whereas having clause can accept the aggregate functions like sum, count, average, etc.
Let’s see it by example.
| emp_salary_paid | |||
| paid_id | emp_id | salary_given | salary_month |
|---|---|---|---|
| 1 | 1 | 1000 | jan |
| 2 | 2 | 1500 | jan |
| 3 | 3 | 2000 | jan |
| 4 | 1 | 1200 | feb |
| 5 | 2 | 1500 | feb |
| 6 | 3 | 1800 | feb |
| emp_detail | |
| emp_id | emp_name |
|---|---|
| 1 | Sajid |
| 2 | John |
| 3 | Ahmad |
Query:
- SELECT emp_detail.emp_name, emp_salary_paid.salary_month, emp_salary_paid.salary_given
- FROM emp_detail, emp_salary_paid
- WHERE emp_detail.emp_id=emp_salary_paid.emp_id
will produce result like this one. because no aggregate function is being used here.
| emp_name | salary_month | salary_given |
|---|---|---|
| Sajid | jan | 1000 |
| Sajid | feb | 1200 |
| John | jan | 1500 |
| John | feb | 1500 |
| Ahmad | jan | 2000 |
| Ahmad | feb | 1800 |
- SELECT emp_detail.emp_name, SUM(emp_salary_paid.salary_given) AS total_paid
- FROM emp_detail, emp_salary_paid
- GROUP BY emp_detail.emp_id
- WHERE SUM(emp_salary_paid.salary_given) >2000
This will produce an error, because aggregate function is being used with where clause. To overcome this, we use having clause like this one.
- SELECT emp_detail.emp_name, SUM(emp_salary_paid.salary_given)
- FROM emp_detail, emp_salary_paid
- GROUP BY emp_detail.emp_id
- HAVING SUM(emp_salary_paid.salary_given) > 2000
This will produce the correct result as follows:
| emp_name | total_paid |
|---|---|
| Sajid | 9000 |
| John | 9000 |
| Ahmad | 9000 |