Grouping by multiple columns

As its name implies, the GROUP BY clause serves to group a collection of table records based on the values of one or more columns. For each group of rows having the same value(s) in the grouping expression(s), one single result row is generated.

Within an SQL statement, the SQL command GROUP BY is optional. An SQL query including a GROUP BY clause is called a grouped query. When multiple columns are included in a GROUP BY clause, they are separated by a comma ",".

In a GROUP BY clause, the sequence of columns matters only when using extensions like Rollup and Cube. The HAVING condition is optional and can be used in conjunction with a GROUP BY clause to restrict the rows affected by the GROUP BY clause to certain given criteria.

The order of data being generated after executing an SQL statement containing a GROUP BY clause is pointless unless explicitly specified by an ORDER BY clause.

Purpose

The GROUP BY clause arranges data into groups and helps mainly in producing summarized data reports, especially when accompanied by aggregate functions such as COUNT, AVG, MIN, and MAX.

Examples

Let's look at the following example demonstrating how the GROUP BY clause operates when using multiple columns.

We'll use the following inline table to illustrate how the GROUP BY clause works:

Let's group data based on the columns Department and Degree:

Let's group data based on the columns Department and Degree while counting the number of employees in each group using the aggregate function count: