Ordering
Limiting the output
Eliminating duplicate results
Aggregation
Grouping
18. Group the rows and count them
HAVING: filtering and ordering groups
Let's practice

Instruction

In the previous section, we learned how to count statistics for all rows (aggregation). We'll now go on to study even more sophisticated statistics. Look at the following statement:

SELECT
  CustomerId,
  Count(*) AS CustomerOrders
FROM Order
GROUP BY CustomerId;

The new piece here is GROUP BY followed by a column name (CustomerId). GROUP BY will group together all rows having the same value in the specified column.

In our example, all orders made by the same customer will be grouped together in one row. The function COUNT(*) will then count all rows for the specific clients. As a result, we'll get a table where each CustomerId will be shown together with the number of orders placed by that customer.

Take a look at the following table that illustrates the query:

OrderId CustomerId OrderDate ShipDate TotalSum CustomerId COUNT(*)
1 1 21-02-2014 22-02-2014 1009.00 1 3
2 1 25-02-2014 25-02-2014 2100.00    
3 1 03-03-2014 03-03-2014 315.00    
4 2 03-03-2014 04-03-2014 401.67 2 2
5 2 03-03-2014 07-03-2014 329.29    
6 3 15-03-2014 15-03-2014 25349.68 3 1
7 4 19-03-2014 20-03-2014 2324.32 4 4
8 4 02-04-2014 02-04-2014 7542.21    
9 4 05-04-2014 07-04-2014 123.23    
10 4 05-04-2014 07-04-2014 425.33    
11 5 06-04-2014 09-04-2014 2134.65 5 5
12 5 17-04-2014 19-04-2014 23.21    
13 5 25-04-2014 26-04-2014 5423.23    
14 5 29-04-2014 30-04-2014 4422.11    
15 5 30-04-2014 30-04-2014 532.54  

Exercise

Find the number of employees in each department in the year 2013. Show the department name together with the number of employees in that department in 2013. Name the last column DepartmentEmployees.

Stuck? Here's a hint!

Type:

SELECT
  Department,
  COUNT(*) AS DepartmentEmployees
FROM Employee
WHERE Year = 2013
GROUP BY Department;