HAVING

SELECT with a HAVING clause

In this exercise we will query the products table, group the results and return only those that have a category_id of "5".

SELECT product_id,
       product_name,
       unit_price,
       category_id
FROM products
GROUP BY product_id,
         product_name,
         unit_price,
         category_id
HAVING category_id=5;

This query should return 7 rows

SELECT with a HAVING clause and a function

In this exercise we will query the products table, group the results and return only those unit_price when multiplied by units_in_stock is greater than $2800.

SELECT product_name,
       sum(unit_price * units_in_stock),
       units_in_stock
FROM products
GROUP BY product_name,
         unit_price,
         units_in_stock
HAVING unit_price * units_in_stock > 2800;

This query should return 7 rows

SELECT with a HAVING clause and COUNT

In this exercise we will query the products table, group the results and return only those unit_price greater than 28.

SELECT product_name,
       sum(unit_price) units_in_stock
FROM products
GROUP BY product_name,
         unit_price,
         units_in_stock
HAVING unit_price > 28

This query should return 26 rows

SELECT with a HAVING clause and a less than operator

In this exercise we will query the products table, group the results and return only those whose category_id is less than 4.

SELECT product_id,
       product_name,
       unit_price,
       category_id
FROM products
GROUP BY product_id,
         product_name,
         unit_price,
         category_id
HAVING category_id < 4;

This query should return 37 rows