/    /  MySQL – GROUP BY Clause

MySQL – GROUP BY Clause

 

my1

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;

my2

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;

my3

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;

my4
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;

my5