MySql Having Clause

The previous lesson of MySql Lessons Series was about MySql Replace Function and in this lesson we will learn MySql Having Clause.



MySql having clause is necessary where we need to apply conditions on the aggregate functions like SUM, AVG, COUNT etc. Mysql where clause do not apply on aggregate functions. The basic structure of mysql having clause is given below:

SELECT column(s) FROM table GROUP BY column_name 
HAVING condition

Consider the transactions table shown in the image below:



Now let us create a situation where we will need to use having clause in mysql query to fetch the required data. Let us say we want to fetch the users from transactions table whose total transactions sum is greater than 100 i.e

SELECT Name FROM `transactions` GROUP BY `Name` 
HAVING SUM(transaction) > 100

The above query will fetch the names of John and Michelle because if we sum all the transactions of John and Michelle, it will exceed 100 for both of them.

Use of Where and Having Clause in the same query:

We can use where clause with the having clause if we need more specific results. Let us modify the condition in the above query a bit to fit the where clause in. If we want to fetch the users from transactions table whose total transactions sum is greater than 100 and only those transactions should be included whose status is Approved, then we need to use where clause along with having clause in the same mysql query i.e

SELECT Name FROM `transactions` WHERE `status` = 'Approved' 
GROUP BY `Name` HAVING SUM(transaction) > 100

The above query will yield only one result i.e John because Michelle has one transaction with rejected status which makes the total of her transactions with approved status less than 100.

For more lessons on MySql subscribe on InformationBitz.com