What is MySQL GROUP BY clause?

The group by clause:

• Used in the select statement
• It is used to group the rows with the same values
• Optionally and generally, the GROUP BY is used with the MySQL aggregate functions. For example, SUM, COUNT, MAX etc.
• The result-set returned by GROUP BY clause can be based on one or more columns.
• The result-set returned by GROUP BY clause can be filtered by using the HAVING clause
• The ORDER BY clause (if required) is used after the GROUP BY clause

Syntax of using MySQL GROUP BY clause

This is how you may use the GROUP BY statement in MySQL:

SELECT

col1, col2, col3, …[aggregate_function(col)]

FROM

table_name

WHERE Condition

GROUP BY col1 , col2,…

ORDER BY col(s);

The points to note as using GROUP BY statement:

• You may provide one or more columns in the SELECT statement
• Aggregate functions can be used in the select statement
• Just like an ordinary SELECT statement, the WHERE clause can be used after the SELECT statement
• This is followed by using the GROUP BY clause and there you will provide the column name(s).
• If you require to sort the result-set in ascending or descending order then use the ORDER BY clause after the GROUP BY.

The examples of using GROUP BY clause

For showing how GROUP BY clause works, we are using a table that contains the records of employee’s salaries paid every month in a departmental store. The table name is tst_emp_paid_sal and it contains the following columns with description:

• emp_id = ID of the employee that relates to tst_employee table
• emp_name = Though this shold be in tst_employee table only, I used this for the demo only for simplifying examples.
• emp_sal_paid = this is the salary paid to the employee for a specific month
• date_paid = The date when the salary was paid.

The query for getting total salaries paid to each employee

The first query will return a result-set for all employees in this table with the SUM of salary paid. For that, we will group the data based on employee IDs and get the total of salary paid for each employee. For getting the total, I used the SUM function as follows:

The GROUP BY Query:

SELECT employee_name, SUM( employee_salary_paid ) AS  "Total Paid"

FROM tst_emp_paid_sal

GROUP BY employee_name

Complete table data/ with GROUP BY and SUM:

In the above graphic, you can see first query retrievs full demo table data by using a simple SELECT statement. The other GROUP BY query returns the result-set for each employee’s salary total.

Keep an eye on the complete table data as the next examples will show only demo query record-sets.

Querying a specific employee’s total salary by GROUP BY

This is just like the above query except I used the WHERE clause for retrieving the record of only employee ‘Mike’. The condition is applied in the WHERE clause as shown below:

SELECT employee_name AS  "Employee Name", SUM( employee_salary_paid ) AS  "Total Paid"

FROM tst_emp_paid_sal

WHERE employee_name =  'Mike'

GROUP BY employee_name

The result:

Maximum salary paid to each employee example

By MAX function, we will get the maximum salary paid to each employee using SELECT/GROUP BY statement:

SELECT employee_name AS  "Employee Name", MAX( employee_salary_paid ) AS  "Maximum Salary"

FROM tst_emp_paid_sal

GROUP BY employee_name

The record-set:

You may compare this result-set with the first graphic with complete table data and see the maximum salary paid to each employee.

Minimum salary query

Similarly, by using the MIN function, we may get the minimum salary paid to each employee:

SELECT employee_name AS  "Employee Name", MIN( employee_salary_paid ) AS  "Lowest Paid Salary"

FROM tst_emp_paid_sal

GROUP BY employee_name

The resultant table:

Count of salaries for each employee using GROUP BY

The COUNT function can also be used with the GROUP BY clause. In that case, rather than returning the total count of all rows, the COUNT will return the number of times the salary paid to each employee.

Thus the mix of GROUP BY and COUNT will return the count of each group result rather than a single value for the whole record-set. The example below shows how:

SELECT employee_name AS  "Employee Name", COUNT( employee_salary_paid ) AS  "No. Of Salaries"

FROM tst_emp_paid_sal

GROUP BY employee_name

The result-set:

A query using MySQL GROUP BY with HAVING clause

In one of the above examples, we used WHERE clause to filter the record of an employee. What if you require to return the result based on the aggregated data. For example, only displaying the records of those employees which SUM of salary exceeds 5000 or more.

For that, you may use the HAVING clause with the GROUP BY as shown in a query below:

SELECT employee_name, SUM( employee_salary_paid ) AS  "Total Paid"

FROM tst_emp_paid_sal

GROUP BY employee_name

HAVING SUM( employee_salary_paid ) >= 5000

The result:

You can see, the first result-set shows all rerecords without the HAVING clause. The second result-set displays the records for those employees only whom total of paid salary is greater than or equal to 5000.