MySQL GROUP BY

Grouping data is an essential operation in database applications. Grouping means summarization of data using aggregate functions on the basis of columns other than those used in aggregate functions.  MySQL GROUP BY is a clause added to MySQL SELECT Query to summarize the rows of data.

Consider these examples where MySQL GROUP BY clause will be applicable.

  • Find the department wise total salary in an organization.
  • Find maximum salary in each department of an organization.
  • Find class wise topper student name in a table containing marks details of all the students.

Syntax of MySQL GROUP BY

GROUP BY clause is used in a SELECT statement. It is added after WHERE clause if you need to group filtered records.

SELECT [All| DISTINCT ] Column Name1 AS alias,Column Name2,…, [SUM|AVG|MIN|MAX|COUNT(Column Name11,)…]FROM table name [WHERE condition expression] [GROUP BY Column Name1, Column Name2,… ]

  • SELECT, All, DISTINCT, FROM and WHERE are the keywords used in MySQL SELECT statement. ALL keyword will display duplicate records and DISTINCT keyword will display only distinct rows if duplicate rows are stored in the table.
  • Column Name1, Column Name2,… – are  the columns on which you want to group the data give as  the output of SELECT statement. Columns names can be given in any order. Data will be presented in the sequence of columns specified in the MySQL Select statement overriding the sequence of columns in a table. AS keyword followed by an alias is used to give a different heading name to output column.
  • table name is the name of the table to access data from.
  • SUM|AVG|MIN|MAX|COUNT are the MySQL aggregate functions.
  • GROUP BY clause is followed by columns names listed in SELECT part of query other than those used in the Aggregate functions.
  • Condition expression in the WHERE is the filter to choose specific rows.

Examples of MySQL GROUP BY

Find department wise total salaries

The following query displays total salaries for all the departments (department_id column)

SELECT department_id , SUM(salary) FROM employees GROUP BY department_id;
MySQL Group By

Find Job  wise total salaries

This query displays job_id wise total salaries in the company.

SELECT job_id , SUM(salary) FROM employees GROUP BY job_id;
group by

Using WHERE clause to Filter Data Rows

This query finds job wise total salaries only for employees having salaries greater than 5000 using WHERE Clause. Note that WHERE clause is used before GROUP BY clause

SELECT job_id , SUM(salary) FROM employees WHERE SALARY>5000 GROUP BY job_id;

Using ORDER BY clause to Sort Summarized output

This query displays job wise total salaries for employees having salaries greater than 5000 and order the output descending order of job_id.

SELECT job_id , MAX(salary) FROM employees WHERE SALARY>5000 GROUP BY job_id ORDER BY job_id;

Points to remember

  • The non aggregate column names in the SELECT part of the query must be included in the GROUP BY clause.
  • WHERE clause is used to filter and include specific rows for summarizing the table data.
  • You can order the summarized output by using ORDER BY clause after GROUP BY Clause.