MySQL GROUP BY

Summary: In this chapter, we will learn how to use MySQL GROUP BY to group rows into subgroups based on values of columns or expressions.

Introducing to MySQL GROUP BY clause

The GROUP BY clause, which is an optional part of the SELECT statement, groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set.

The GROUP BY statement is used with an aggregate function (like: count, max, min, sum, avg) to group the result-set by one or more columns.

The following illustrates the GROUP BY clause syntax:

1 2 3 4 5 6

SELECT   column_name, aggregate_function(column_name) FROM   table_name WHERE column_name operator value GROUP BY column_name;

MySQL GROUP BY examples

We will continue with the employees table data in the sample database as shown in the picture below.

Suppose we want to group values of the job profile into subgroups, we use the GROUP BY clause with the jobtitle column as the following query:

1 2 3 4

SELECT   jobtitle FROM employees GROUP BY jobtitle;

We can see that the GROUP BY clause returns unique occurrences of jobtitle values. We should use a aggregate functions which is allow us to perform the calculation of a set of rows and return a single value. The GROUP BY clause is often used with an aggregate function to perform calculation and return a single value for each subgroup.

For example, if we want to know how much salary is depositing based on job profile, we can use the SUM function with the GROUP BY clause as follows:

1 2 3 4

SELECT   jobtitle, SUM(salary) FROM employees GROUP BY jobtitle;

In this chapter, we have learnt how to use the MySQL GROUP BY clause to group rows into subgroups based on values of columns or expressions.

---

MySQL SELF JOIN MySQL HAVING

---