Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
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've learned how to count statistics for all rows. We'll now go on to study even more sophisticated statistics. Look at the following statement:

SELECT
  customer_id,
  COUNT(*)
FROM orders
GROUP BY customer_id;

The new piece here is GROUP BY followed by a column name (customer_id). 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 customer_id will be shown together with the number of orders placed by that customer.

Take a look at the following table which illustrates the query (expand the column or scroll the table horizontally if you need to):

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

Exercise

Find the number of employees in each department in 2013. Show the department name together with the number of employees. Name the second column employees_no.

Stuck? Here's a hint!

Type:

SELECT
  department,
  COUNT(*) AS employees_no
FROM employees
WHERE year = 2013
GROUP BY department;