9. HAVING Clause

The HAVING clause allows you to specify conditions on the rows for each group - in other words, which rows should be selected based on the conditions you specify. The HAVING clause should follow the GROUP BY clause if you are going to use it.

9.1. Syntax

  SELECT column1, SUM(column2)
  FROM "list-of-tables"
  GROUP BY "column-list"
  HAVING "condition";
  

9.2. Example 1

Consider an employee table containing the employee's name, department, salary, and age. If you would like to select the average salary for each department, you could enter:

  SELECT dept, AVG(salary)
  FROM employee
  GROUP BY dept;
  

To only calculate and display the average if the average salary is over 20000, you would use the HAVING clause:

  SELECT dept, AVG(salary)
  FROM employee
  GROUP BY dept
  HAVING AVG(salary) > 20000;
  

9.3. Review Exercises

Use the following tables for the exercises:

• items_ordered
• customers

9.3.1. Exercise 1

How many people are in each unique state in the customers table that have more than one person in the state? Select the state and display the number of people in each if it's greater than 1.

  SELECT state, COUNT(state)
  FROM customers
  GROUP BY state
  HAVING COUNT(state) > 1;
  

9.3.2. Exercise 2

From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00.

  SELECT item, MAX(price), MIN(price)
  FROM items_ordered
  GROUP BY item
  HAVING MAX(price) > 190.00;
  

9.3.3. Exercise 3

How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders if they purchased more than 1 item.

  SELECT customerid, COUNT(customerid), SUM(price)
  FROM items_ordered
  GROUP BY customerid
  HAVING COUNT(customerid) > 1;