I’ve got two tables for you:
1. Lecturers (LectID, Fname, Lname, degree). 2. Lecturers_Specialization (LectID, Expertise).
I’m looking for the professor with the greatest specialized knowledge. This does not work when I attempt it:
SELECT L.LectID, Fname, Lname FROM Lecturers L, Lecturers_Specialization S WHERE L.LectID = S.LectID AND COUNT(S.Expertise) >= ALL (SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID);
However, when I try it, it works:
SELECT L.LectID, Fname, Lname FROM Lecturers L, Lecturers_Specialization S WHERE L.LectID = S.LectID GROUP BY L.LectID, Fname, Lname HAVING COUNT(S.Expertise) >= ALL (SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID);
What is the explanation for this? Thanks.
Asked by Adam Sh
The WHERE clause adds a condition to individual rows; the HAVING clause adds a condition to aggregations, or outcomes of selection where a single result, such as count, average, min, max, or total, is derived from numerous rows. Because your query requires a second type of condition (a condition on an aggregation), HAVING works.
Use WHERE before GROUP BY and HAVING after GROUP BY as a rule of thumb. It’s a simple rule, yet it works in more than 90% of the circumstances.
While you’re about it, you might as well rewrite your query using the ANSI join:
SELECT L.LectID, Fname, Lname FROM Lecturers L JOIN Lecturers_Specialization S ON L.LectID=S.LectID GROUP BY L.LectID, Fname, Lname HAVING COUNT(S.Expertise)>=ALL (SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)
WHERE would be removed as a theta join condition as a result of this.
Answered by Sergey Kalinichenko
We must first determine the sequence in which Clauses are executed, i.e. FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY. Because the WHERE Clause executes before the GROUP BY Clause, records can’t be filtered by using WHERE on GROUP BY records.
“The HAVING clause is similar to the WHERE clause, but it is used on grouped records.”
The WHERE clause retrieves records based on a condition, then the GROUP BY clause groups them, and finally the HAVING clause retrieves group records depending on the having condition.
Answered by Pardhu
HAVING works with aggregates. You can’t use COUNT in a WHERE clause because it’s an aggregate function.
Here’s some information about aggregate functions from MSDN.
Answered by Daniel Mann
Answered by Venkata Krishna Reddy
I couldn’t find an example of both in a single query. As a result, this example may be useful.
/** INTERNATIONAL_ORDERS - table of orders by company by location by day companyId, country, city, total, date **/ SELECT country, city, sum(total) totalCityOrders FROM INTERNATIONAL_ORDERS with (nolock) WHERE companyId = 884501253109 GROUP BY country, city HAVING country = 'MX' ORDER BY sum(total) DESC
s first filters the table by companyId, then groups it (by nation and city), and finally narrows it down to only Mexican city aggregations. Although the companyId was not required for the aggregation, we were able to filter out only the items we wanted before applying GROUP BY by using WHERE.
Answered by Nhan
Post is based on https://stackoverflow.com/questions/9253244/sql-having-vs-where