Ordering
Limiting the output
Eliminating duplicate results
Aggregation
Grouping
HAVING: filtering and ordering groups
22. 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 that:

SELECT
  CustomerId,
  OrderDate,
  SUM(TotalSum) AS CustomerDailySum
FROM Order
GROUP BY CustomerId,
  OrderDate
HAVING SUM(TotalSum) > 2000;

The new part here comes at the end. We've put the HAVING keyword and then stated the condition on which to filter the groups. In this case, we only want to show those customers who, on individuals days, ordered goods for a total daily value exceeding 2000.

By the way, this is probably a good moment to note an important point: in T-SQL, the specific clauses must be always 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 all employees who have spent more than 2 years at the company. Select their last name and first name together with the number of years they have worked. Name the column EmployeeYears.

Stuck? Here's a hint!

Type:

SELECT
 LastName,
 FirstName,
 COUNT(*) AS EmployeeYears
FROM Employee
GROUP BY LastName, FirstName
HAVING COUNT(*) > 2;