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;
