It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Ordering
Eliminating duplicate results
Aggregation
Grouping
HAVING: filtering and ordering groups
20. Filter groups
Let's practice

Instruction

In this section, we'll have a look at how groups can be filtered. There is a special keyword HAVING reserved for this.

SELECT
  customer_id,
  order_date,
  SUM(total_sum)
FROM orders
GROUP BY customer_id, order_date
HAVING SUM(total_sum) > 2000;

The new part here comes at the end. We've used the keyword HAVING and then stated the condition to filter the results. In this case, we only want to show those customers who, on individuals days, ordered goods with a total daily value of more than $2,000.

By the way, this is probably a good time to point out an important thing: in SQL, the specific fragments must always be put in the right order. You can't, for example, put WHERE before FROM. Similarly, HAVING must always follow GROUP BY, not the other way around. Keep that in mind when you write your queries, especially longer ones.

Exercise

Find such employees who (have) spent more than 2 years in the company. Select their last name and first name together with the number of years worked (name this column years).

Stuck? Here's a hint!

Type:

SELECT
  last_name,
  first_name,
  COUNT(DISTINCT year) AS years
FROM employees
GROUP BY last_name, first_name
HAVING COUNT(DISTINCT year) > 2;

Note that we need to use DISTINCT because a job can be changed during a year.