MySQL Group by Clause

In many scenarios, you need aggregated data for reporting or other reasons to make decisions. For instance, performance reports based on employees data for a specific period. The group by clause of MySQL helps in performing aggregate functions by grouping column values.

In this chapter, we will explain what is group by clause of MySQL and how to use it with examples.

What is group by clause in MySQL?

• MySQL has aggregate functions like Sum, Min, Max etc. The group by clause works with aggregate functions.
• You have to specify column names in the group by clause.
• Group by is used in the Select statement of MySQL.
• If you are are using Where clause in the select query, the group by will be used after the where clause.
• Whereas if your query includes the order by clause then group by will be used before it.

Group by clause syntax

The syntax to use MySQL group by clause is:

Select col1, col2, aggregate_function(column_name) from table_name

Where col1= val1            

Group by column_name

Order by column_name

Group by clause examples

In our examples, we will use the tbl_emp_salary_paid table that stores employees salaries. We have already created that table. Click the link below to see its data that we will use for grouping.

Table to be used with data in Group by

An example of Sum function with Group by

In the following example, we will use the sum function at the emp_sal_paid column. This query will return total salary paid overall to each employee.

The MySQL group by query:

You can see in the query, first emp_name column with same names will be grouped. Then the sum function will be used on the emp_sal_paid column. The result-set will be total salary paid to each employee.

Group by example with Min function

The following query will return minimum salary paid to each employee.

The group by with min function query:

An example of count with group by clause

This query will return the total number of times salary paid to each employee by using the count aggregate function with the group by clause.

The Group by query: