Grouping results (aggregate functions)
You can group the results using the GROUP BY clause on a column and then use AGGREGATE functions, such as COUNT, MAX, MIN, and AVERAGE. You can also use the function on a column in a group by clause. See the SUM example where you will use the YEAR() function.
COUNT
Find the count of male and female employees:
SELECT gender, COUNT(*) AS count FROM employees GROUP BY gender;
+--------+--------+
| gender | count |
+--------+--------+
| M | 179973 |
| F | 120051 |
+--------+--------+
2 rows in set (0.24 sec)
You want to find the 10 most common first names of the employees. You can use GROUP BY first_name to group all the first names, then COUNT(first_name) to find the count inside the group, and finally the ORDER BY count to sort the results. LIMIT these results to the top 10:
SELECT first_name, COUNT(first_name) AS count FROM employees GROUP BY first_name ORDER BY count DESC LIMIT 10;
+-------------+-------+
| first_name | count |
+-------------+-------+
| Shahab | 295 |
| Tetsushi | 291 |
| Elgin | 279 |
| Anyuan | 278 |
| Huican | 276 |
| Make | 275 |
| Sreekrishna | 272 |
| Panayotis | 272 |
| Hatem | 271 |
| Shen | 270 |
+-------------+-------+
10 rows in set (0.35 sec)
SUM
The YEAR() function returns the YEAR of the given date:
SELECT '2017-06-12', YEAR('2017-06-12');
+------------+--------------------+
| 2017-06-12 | YEAR('2017-06-12') |
+------------+--------------------+
| 2017-06-12 | 2017 |
+------------+--------------------+
1 row in set (0.00 sec)
Find the sum of the salaries given to employees in each year and sort the results by salary.
SELECT YEAR(from_date), SUM(salary) AS sum FROM salaries GROUP BY YEAR(from_date) ORDER BY sum DESC;
+-----------------+-------------+
| YEAR(from_date) | sum |
+-----------------+-------------+
| 2000 | 17535667603 |
| 2001 | 17507737308 |
| 1999 | 17360258862 |
| 1998 | 16220495471 |
| 1997 | 15056011781 |
| 1996 | 13888587737 |
| 1995 | 12638817464 |
| 1994 | 11429450113 |
| 2002 | 10243347616 |
| 1993 | 10215059054 |
| 1992 | 9027872610 |
| 1991 | 7798804412 |
| 1990 | 6626146391 |
| 1989 | 5454260439 |
| 1988 | 4295598688 |
| 1987 | 3156881054 |
| 1986 | 2052895941 |
| 1985 | 972864875 |
+-----------------+-------------+
18 rows in set (2.36 sec)
AVERAGE
Find the 10 employees with the highest average salaries:
SELECT emp_no, AVG(salary) AS avg FROM salaries GROUP BY emp_no ORDER BY avg DESC LIMIT 10;
+--------+-------------+
| emp_no | avg |
+--------+-------------+
| 109334 | 141835.3333 |
| 205000 | 141064.6364 |
| 43624 | 138492.9444 |
| 493158 | 138312.8750 |
| 37558 | 138215.8571 |
| 276633 | 136711.7333 |
| 238117 | 136026.2000 |
| 46439 | 135747.7333 |
| 254466 | 135541.0625 |
| 253939 | 135042.2500 |
+--------+-------------+
10 rows in set (1.42 sec)
DISTINCT
You can use the DISTINCT clause to filter the distinct entries in a table:
SELECT DISTINCT title FROM titles;
+--------------------+
| title |
+--------------------+
| Senior Engineer |
| Staff |
| Engineer |
| Senior Staff |
| Assistant Engineer |
| Technique Leader |
| Manager |
+--------------------+
7 rows in set (0.31 sec)
Filtering using HAVING
You can filter results of the GROUP BY clause by adding the HAVING clause.
For example, find the employees with an average salary of more than 140,000:
SELECT emp_no, AVG(salary) AS avg FROM salaries GROUP BY emp_no HAVING avg > 140000 ORDER BY avg DESC;
+--------+-------------+
| emp_no | avg |
+--------+-------------+
| 109334 | 141835.3333 |
| 205000 | 141064.6364 |
+--------+-------------+
2 rows in set (1.32 sec)
There are many other aggregate functions, refer to https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html for more information.