SQL Having

 

 

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.