Tag Archives: Having

GROUP BY, Where vs Having

GROUP BY is actually used to group the result-set by using aggregate functions.

Let’s say we have a table Student with columns:

Name Subject Score
Mahesh Math 80
Mahesh Eng 70
Ramesh Eng 70
Somesh Math 65
Ramesh Math 75
Somesh Eng 65
Mahesh Hindi 60
Ramesh Hindi 75

Now to get each student total marks:

SELECT Name, Sum(Score)

FROM Student

GROUP BY Name

Name Score
Mahesh 210
Ramesh 220
Somesh 130

Without GROUP BY,

SELECT Name, Sum(Score)

FROM Student

Name Score
Mahesh 560
Mahesh 560
Ramesh 560
Somesh 560
Ramesh 560
Somesh 560
Mahesh 560
Ramesh 560

WHERE vs HAVING

WHERE HAVING
WHERE is basically used to filter before the Group BY. HAVING is used to filter on GROUP BY result-set.
WHERE can’t be used with aggregate functions. Having is applied with aggregate functions only.
WHERE doesn’t need Group By. HAVING is used with Group By only.

Now for Query to get all students who have appeared in three subject exam:

SELECT Name, Count (Name)

FROM Student

GROUP BY Name

HAVING Count (Name) =3

To get all students who have scored more then 70 & appeared in three subject exam:

SELECT Name, Count(Name)

FROM Student

Where Score>70

GROUP BY Name

HAVING Count(Name)=3

 References:

http://www.programmerinterview.com/index.php/database-sql/having-vs-where-clause/

Advertisements

Leave a comment

Filed under SQL