SQL: HAVING Clause
The SQL
HAVING clause is used in combination with the SQL
GROUP BY clause. It can be used in an SQL
SELECT statement to filter the records that a SQL GROUP BY
returns.
The syntax
for the SQL HAVING clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;
aggregate_function can be a function such as SQL
SUM function, SQL COUNT function, SQL
MIN function, or SQL
MAX function.
SQL HAVING Clause - Using the SUM function example
You could
also use the SQL SUM function to return the name of
the department and the total sales (in the associated department). The SQL
HAVING clause will filter the results so that only departments with sales
greater than $1000 will be returned.
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
SQL HAVING Clause - Using the COUNT function example
You could use
the SQL COUNT function to return the name
of the department and the number of employees (in the associated department)
that make over $25,000 / year. The SQL HAVING clause will filter the results so
that only departments with more than 10 employees will be returned.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;
SQL HAVING Clause - Using the MIN function example
You could
also use the SQL MIN function to return the name of
each department and the minimum salary in the department. The SQL HAVING clause
will return only those departments where the starting salary is $35,000.
SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;
SQL HAVING Clause - Using the MAX function
For example,
you could also use the SQL MAX function to return the name of
each department and the maximum salary in the department. The SQL HAVING clause
will return only those departments whose maximum salary is less than $50,000.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;
No comments:
Post a Comment