| SQL Group By | Examples of SQL Group By and SQL Having | Null in SQL Group By |
The SQL HAVING clause allows us to restrict the data that is sent to the GROUP BY clause.
Group functions cannot be used in the WHERE clause. SQL statement can have both a WHERE clause and an HAVING clause. WHERE filters data before grouping and HAVING filters the data after grouping.
A WHERE clause is useful in both grouped and ungrouped queries, while a HAVING clause should appear only immediately after the GROUP BY clause in a grouped query.
According to Wikipedia (http://en.wikipedia.org) HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions.
An SQL statement with the HAVING clause may or may not include the GROUP BY clause.
HAVING allows a user to perform conditional tests on aggregate values. It is often used in combination with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.
Example 1 of SQL Having
Find the average salary of for each department that has either more than 1 employee or starts with a “To”:
SELECT Dept, AvgSal=(AVG(Salary))
FROM Employee
GROUP BY Dept
HAVING COUNT(Name) > 1 OR Dept LIKE “To”
Example 2 of SQL Having
Workforce (workforceno, name, position, salary, email, dcenterno)
For each distribution center with more than one member of workforce, find the number of workforce working in each of the centers and the sum of their salaries.
SELECT dCenterNo, COUNT(workforceNo) AS totalworkforce,
SUM(salary) AS totalSalary
FROM workforce
GROUP BY dCenterNo
HAVING COUNT(workforceNo) > 1
ORDER BY dCenterNo;
Important points about SQL Having:
Aggregates cannot be used in a WHERE clause; they are used only inside HAVING.
Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause.
Similar to the WHERE clause, it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions.