Database Related

Differentiation Between The Having Clause And The Group By Statement

In SQL, the group by statement and having clause work together when using aggregate functions like MAX, SUM, AVG, etc. The difference between the ‘group by statement’ and ‘having’ clause is best demonstrated by an example. Just suppose a table named as lab_bonus as shown below. Note that the table has several entries for labors A and B.

Lab bonus

Labors Bonus
A 2000
B 4000
A 1000
C 1400
B 2500

 

If you want to calculate the total bonus that each labor received, then you have to write a SQL statement like this

  • select labor, sum(bonus) from lab_bonus group by labor;

The Group By Clause

In the above SQL statement, you can see that we have used the “group by” clause with the labor column. The group by clause allows you to find the sum of the bonuses for each labor. When you use the ‘sum(bonus)’  in combination with the ‘group by’ statement, this will give you the sum of all the bonuses for labors A, B, and C.

 

After running the above SQL, you will get this:

Labor Sum(Bonus)
A 3000
B 6500
C 1400

 

Now, suppose you wanted to find the labors who received more than $2,000 bonuses in 2007. You might be thinking to write a query like this:

 

BAD SQL:

select labor, sum(bonus) from lab_bonus

group by labor where sum(bonus) > 2000;

 

The above SQL will not work. This is because the where clause does not work with aggregates, such as max, avg, sum, etc. Rather, you have to use the having clause. The having clause was inserted to SQL so that you could match aggregates to other values and see that how the ‘where’ clause can be utilized with non-aggregates. Finally, the correct SQL will appear like this

 

GOOD SQL:

select labor, sum(bonus) from lab_bonus

group by labor having sum(bonus) > 2000;

 

 

After running the above SQL, you will get this:

Labor Sum(Bonus)
A 3000
B 6500

 

Difference Between Group By Statement And Having Clause

You can clearly see from the above example that the group by clause is used with group column(s), thus aggregates (such as MAX, SUM) can be used for finding the necessary information.

However, the having clause is used with the group by clause while comparisons required to be made using those aggregate functions, for example, to check if the SUM is greater than 2,000, as in our above example. Therefore, group by statements and the having clause is not really alternatives to each other; however they are used alongside one another.

 

 

Leave a Reply