IGNOU BCA 3rd sem Solved Assignment - Explain how the “GROUP By” clause works. What is the difference between the WHERE and HAVING clauses? Explain them with the help of an example for each.

Explain how the “GROUP By” clause works. What is the difference between the WHERE and HAVING clauses? Explain them with the help of an example for each.

 Ans

Ans

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:

GROUP BY clause syntax:

SELECT column1,

SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list";

Let's say you would like to retrieve a list of the highest paid salaries in each dept:

SELECT max(salary), dept

FROM employee

GROUP BY dept;

This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.

The difference between the having and where clause 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. What the group by clause does is allow us to find the sum of the bonuses for eachemployee. 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. 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. You might think that we could write a query like this: BAD SQL: select employee, sum(bonus) from emp_bonus group by employee where sum(bonus) > 1000; The WHERE clause does not work with aggregates like SUM The SQL above will not work, because the where clause doesn’t work with aggregates – like sum, avg, max, etc.. Instead, what we will need to use is the having clause. The having clause was added to sql just so we could compare aggregates to other values – just how the ‘where’ clause can be used with non-aggregates. Now, the correct sql will look like this: GOOD SQL: select employee, sum(bonus) from emp_bonus group by employee having sum(bonus) > 1000; Difference between having and where clause So we can see that the difference between the having and where clause in sql is that the where clause can not be used with aggregates, but the having clause can. One way to think of it is that the having clause is an additional filter to the where clause.