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;
