Course Module 4. Working with databases - Lecture: Grouping by multiple columns

4.1 Grouping columns

What is also very important is that if you group records, then in the section SELECTyou can only specify the columns by which the data is grouped.

Youcan notwrite a type query and specify the nameSELECT column in the section :

SELECT brand, name
FROM product
GROUP BY brand

You have a bunch of rows in the product table with the name field , and it's not clear which row you need to substitute here. Think again: you want to know the number of products of each brand and how the resulting table can get the name of the product?

If you use the operator GROUP BY, then normal rows cannot get into your result, but only columns by which data is grouped and “calculated columns”, such asCOUNT(*)

4.2 Grouping by calculated columns

But you can group by multiple columns and even by calculated columns. Example:

SELECT
YEAR(join_date) AS hire_year,
COUNT(*) AS total
FROM employee
GROUP BY hire_year

And the result of this query:

hire_year	total
2012	1
2013	1
2014	1
2015	2
2018	1

Let's analyze our request.

In the employee table , each employee has a unique hire date, so grouping data by it will not work - there will be one record for each date and grouping will not make much sense. But if we move from the date of hiring to the year of hiring, then it may well be a situation where the company hired several employees in the same year.

Let's see again what this table will look like before grouping:

SELECT
 	id,
 	name,
 	YEAR(join_date) AS hire_year,
 	join_date
FROM employee

And the result of this query:

id	name	hire_year	join_date
1	Ivanov Ivan	2012	2012-06-30
2	Petrov Petr	2013	2013-08-12
3	Ivanov Sergey	2014	2014-01-01
4	Rabinovich Moisha	2015	2015-05-12
5	Kirienko Anastasia	2015	2015-10-10
6	Vaska	2018	2018-11-11

But this resulting table can be grouped by hire_year and find out how many people were hired in a particular year.

4.3 Grouping by multiple columns

Now let's try to find out how many employees we hired in each month of each year. To do this, we need to add not one, but two calculated fields to the query at once - the year of hire (hire_year) and the month of hire (hire_month).

Let's write a query like this:

SELECT
 	id,
 	name,
 	YEAR(join_date) AS hire_year,
 	MONTH(join_date) AS hire_month,
 	join_date
FROM employee

And the result of this query:

id	name	hire_year	hire_month	join_date
1	Ivanov Ivan	2012	6	2012-06-30
2	Petrov Petr	2013	8	2013-08-12
3	Ivanov Sergey	2014	1	2014-01-01
4	Rabinovich Moisha	2015	5	2015-05-12
5	Kirienko Anastasia	2015	10	2015-10-10
6	Vaska	2018	eleven	2018-11-11

If we group the data in this result table by month, then we simply group people into groups, regardless of the year they were hired. And we need the resulting table to have three columns: year, month, and the number of employees hired.

To do this, after the operator, GROUP BYyou need to use the name of not one column, but two. Example:

SELECT
 	YEAR(join_date) AS hire_year,
 	MONTH(join_date) AS hire_month,
 	COUNT(*) AS total
FROM employee
GROUP BY hire_year, hire_month

And the result of this query:

hire_year	hire_month	total
2012	6	1
2013	8	1
2014	1	1
2015	5	1
2015	10	1
2018	eleven	1

We have few records in the table, so there are units everywhere in the total column. And by the way, note that the more columns we group by, the more rows we have in the result.