SQLite: HAVING Clause
This SQLite tutorial explains how to use the SQLite HAVING clause with syntax and examples.
Description
The SQLite HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.
Syntax
The syntax for the HAVING clause in SQLite is:
SELECT expression1, expression2, ... expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n HAVING condition;
Parameters or Arguments
- expression1, expression2, ... expression_n
- The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.
- aggregate_function
- A function such as sum, count, min, max, or avg functions.
- aggregate_expression
- This is the column or expression that the aggregate_function will be used on.
- tables
- The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
- WHERE conditions
- Optional. These are the conditions for the records to be selected.
- HAVING condition
- This is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.
Example - Using sum function
Let's look at an example that shows how to use the HAVING clause with the sum function in SQLite.
You could use the sum function to return the department and the total salaries (for that department). The SQLite HAVING clause will filter the results so that only departments with total salaries greater than $10,000 will be returned.
SELECT department, sum(salary) AS "Total salaries" FROM employees GROUP BY department HAVING sum(salary) > 10000;
Example - Using count function
Let's look at how to use the HAVING clause with the count function in SQLite.
You could use the count function to return the department and the number of employees (for that department) where the state is 'CA'. The SQLite HAVING clause will filter the results so that only departments with more than 20 employees in the state of 'CA' will be returned.
SELECT department, count(*) AS "Number of employees" FROM employees WHERE state = 'CA' GROUP BY department HAVING COUNT(*) > 20;
Example - Using min function
Let's next look at how to use the HAVING clause with the min function in SQLite.
You could also use the min function to return the name of each department and the minimum salary in the department. The SQLite HAVING clause will return only those departments where the minimum salary is less than $20,000.
SELECT department, min(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) < 20000;
Example - Using max function
Finally, let's look at how to use the HAVING clause with the max function in SQLite.For example, you could also use the max function to return the name of each department and the maximum salary in the department where the employee's favorite_website is 'TechOnTheNet.com'. The SQLite HAVING clause will return only those departments whose maximum salary is greater than or equal to $30,000.
SELECT department, max(salary) AS "Highest salary" FROM employees WHERE favorite_website = "TechOnTheNet.com" GROUP BY department HAVING MAX(salary) >= 30000;
Advertisements