difference between the having clause and the group by statement?
In SQL, the having clause and the group by statement work together when using aggregate functions like SUM, AVG, MAX, etc. This is best illustrated by an example. Suppose we have a table called emp_bonus as shown below. Note that the table has multiple entries for employees A and B.
emp_bonus
Employee Bonus
A 1000
B 2000
A 500
C 700
B 1250
If we want to calculate the total bonus that each employee received, then we would write a SQL statement like this:
select employee, sum(bonus) from emp_bonus group by employee;
The Group By Clause
In the SQL statement above, you can see that we use the "group by" clause with the employee column. The group by clause does allows us to find the sum of the bonuses for each employee. Using the ‘group by’ in combination with the ‘sum(bonus)’ statement will give us the sum of all the bonuses for employees A, B, and C.
Subscribe to our newsletter on the left to receive more free interview questions!
Running the SQL above would return this:
Employee Sum(Bonus)
A 1500
B 3250
C 700
Now, suppose we wanted to find the employees who received more than $1,000 in bonuses for the year of 2007. This is when we need to use the HAVING clause, and this is what the SQL look like:
GOOD SQL:
select employee, sum(bonus) from emp_bonus
group by employee having sum(bonus) > 1000;
And the result of running the SQL above would be this:
Employee Sum(Bonus)
A 1500
B 3250
Difference between having clause and group by statement
So, from the example, we can see that the group by clause is used to group column(s) so that aggregates (like SUM, MAX, etc) can be used to find the necessary information. The having clause is used with the group by clause when comparisons need to be made with those aggregate functions (like “> 1,000″). So, the having clause and group by statements are not really alternatives to each other – but they are used alongside one another!
In SQL, the having clause and the group by statement work together when using aggregate functions like SUM, AVG, MAX, etc. This is best illustrated by an example. Suppose we have a table called emp_bonus as shown below. Note that the table has multiple entries for employees A and B.
emp_bonus
Employee Bonus
A 1000
B 2000
A 500
C 700
B 1250
If we want to calculate the total bonus that each employee received, then we would write a SQL statement like this:
select employee, sum(bonus) from emp_bonus group by employee;
The Group By Clause
In the SQL statement above, you can see that we use the "group by" clause with the employee column. The group by clause does allows us to find the sum of the bonuses for each employee. Using the ‘group by’ in combination with the ‘sum(bonus)’ statement will give us the sum of all the bonuses for employees A, B, and C.
Subscribe to our newsletter on the left to receive more free interview questions!
Running the SQL above would return this:
Employee Sum(Bonus)
A 1500
B 3250
C 700
Now, suppose we wanted to find the employees who received more than $1,000 in bonuses for the year of 2007. This is when we need to use the HAVING clause, and this is what the SQL look like:
GOOD SQL:
select employee, sum(bonus) from emp_bonus
group by employee having sum(bonus) > 1000;
And the result of running the SQL above would be this:
Employee Sum(Bonus)
A 1500
B 3250
Difference between having clause and group by statement
So, from the example, we can see that the group by clause is used to group column(s) so that aggregates (like SUM, MAX, etc) can be used to find the necessary information. The having clause is used with the group by clause when comparisons need to be made with those aggregate functions (like “> 1,000″). So, the having clause and group by statements are not really alternatives to each other – but they are used alongside one another!
