MySQL – GROUP BY Clause
The MySQL GROUP BY clause allows you to collect data from multiple records and group them based on one or more columns. It is generally used as part of a SELECT statement.
As well as using COUNT, SUM, MIN, MAX, AVG, etc. For the grouped columns, you can also use some aggregate functions.
Syntax
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
expression1, expression2, … expression_n – It is necessary to include the GROUP BY clause for expressions that are not encapsulated within an aggregate function.
aggregate_function – This can be a function such as SUM, COUNT, MIN, MAX, or AVG.
Tables – This is the table from which you wish to retrieve records. It is required that at least one table be listed in the FROM clause.
WHERE conditions – This is optional. Conditions that must be met in order to select records.
Example – Using COUNT function
I would like to demonstrate how we can use the GROUP BY clause in MySQL in conjunction with the COUNT function.
SELECT COUNT(salary) FROM employees;
Example – Using SUM function
MySQL GROUP BY example uses the SUM function to return the product name and the total quantity (for the product).
SELECT SUM(salary) FROM employees;
Example – Using MIN function
A GROUP BY example using the MIN function returns the name of each role and the minimum salary for that role.
SELECT last_name, MIN(salary) AS "Lowest salary" FROM employees GROUP BY last_name;
Example – Using MAX function
In this GROUP BY example, the MAX function returns the name of each role and its maximum salary.
SELECT last_name, MAX(salary) AS "Highest salary" FROM employees GROUP BY last_name;