This command is used with GROUP BY to filter group rows. It is similar to WHERE, but HAVING applies to the aggregated rows after the GROUP BY command (WHERE applies to individual rows before the GROUP BY).

The calls below are made a hypothetical transaction database with columns first_name, last_name, customer_id, employee_id, amount, and transaction_quality.

Return the customers who have paid more than $1,000 in the store.

SELECT customer_id, SUM(amount)
FROM transaction
GROUP BY customer_id
HAVING SUM(amount)>1000;

Return the customers who have paid more than $1,000 in purchases of over $100:

SELECT customer_id, SUM(amount)
FROM transaction
WHERE amount > 100
GROUP BY customer_id
HAVING SUM(amount)>1000;

Return customers who have worked with the employee with id equal to 10 and who paid at least $1,000 worth of transactions:

SELECT customer_id, SUM(amount)
FROM transaction
WHERE employee_id=10
GROUP BY customer_id
HAVING SUM(amount)>=1000;

Categories: SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

SQL

UNION

This command combines results from two or more select queries. In order to use the UNION command, you must have the same number of columns in each select statement and the corresponding columns must have Read more…

SQL

GROUP BY

GROUP BY statements use various aggregate functions to combine rows and look at aggregated information. Aggregate functions used below: MIN, MAX, AVG, COUNT, SUM. The calls below are made a hypothetical transaction database with columns Read more…

SQL

Basic SQL Statements

SQL is case-insensitive, but by convention SQL commands are typed in upper case. The examples below return specific data from a hypothetical database named transactions with columns: first_name, last_name, customer_id, and price. Basic commands: SELECT Read more…