Group by Vs Having always give confusion to the new programmers. Here is the detailed work flow of both of the features of SQL.
Example:
Suppose a table of employee_bonus has multiple entries for employees A and B Which means that both employees A and B has received multiple bonuses.
Table Scenario: emp_bonus
Employee Bonus
A 1000
B 2000
A 500
C 700
B 1250
Here if we calculate the sum of total bonus amount that each employee has received.Then a sql statement will be:
select employee, sum(bonus) from emp_bonus group by employee;
GroupBy Clause:
In the SQL statement above, you can see that we use the “group by” clause with the employee column. The group by clause allows us to find the sum of the bonuses for each employee – because each employee is treated as his or her very own group. 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.In the above example we can see that group by is performed on the employee column.
Result will be like:
Employee SUM(Bonus)
A 1500
B 3250
C 700
Here if we want to find employee who has recieved more than $1000 in the year of 2012,here the HAVING clause needs to be used.The sql statement for this scenario would be :
select employee, sum(bonus) from emp_bonus group by employee having sum(bonus) > 1000;
Result will be like :
Employee SUM(bonus)
A 1500
B 3250