HAVING clause

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

HAVING clause syntax:

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

HAVING can best be described by example. Let’s say you have an employee table containing the employee’s name, department, salary, and age. If you would like to select the average salary for each employee in each department, you could enter:

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

But, let’s say that you want to ONLY calculate & display the average if their salary is over 20000:

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

 

Use these tables for the exercises
items_ordered
customers

Review Exercises 

  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 how many people are in each if it’s greater than 1.

    Show Answer

  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.

    Show Answer

  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.

    Show Answer

Enter SQL Statement here:

Results will be displayed here
Previous articleGROUP BY clause
Next articleORDER BY clause

All Advanced Courses