Group by Multiple Columns in SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

To arrange identical data into groups, we use SQL group by clause. The SQL group by single column clause places all the records having the same value of only a particular column into one group. The group by multiple columns technique retrieves grouped column values from one or more database tables by considering more than one column as grouping criteria.

Group By One Column

To arrange similar (identical) data into groups, we use SQL GROUP BY clause. The SQL GROUP BY clause is used along with some aggregate functions to group columns that have the same values in different rows. We generally use the GROUP BY clause with the SELECT statement, WHERE clause, and ORDER BY clauses.

The group by single column places all the records (rows) having the same value of only a particular column into one group.

Syntax

Refer to the image below to visualize grouping.

visualize grouping

In the image above, we have grouped together similar data.

Example

Suppose we are working on a company's database. We have two tables, namely employee and department. The employee table has three columns, namely employee_ID, salary, and department_ID. The department table has two columns, namely department_ID and department_name. Let us try to group the employees present in the employee table based on their department_ID.

employee table:

employee_IDsalarydepartment_ID
100240001
101170001
103100001
10490002
105110002
106160003

department table:

department_IDdepartment_name
1Accounts
1Marketing
2IT

Query:

Output:

salarydepartment_ID
510001
200002
160003

Group by Multiple Columns

The group by multiple columns technique retrieves grouped column values from one or more database tables by considering more than one column as grouping criteria. We use SQL queries to group multiple columns of the database.

The group by multiple columns is used to club together various records with similar (or the same) values for the specified columns. Whenever we perform group by multiple columns (grouping defined on multiple columns), then all the values of those columns should be the same as that of other columns to consider them for grouping into a single record.

We use the GROUP BY clause to implement group by multiple columns. The syntax of the GROUP BY clause is quite simple. Syntax

In the syntax above, we first provide the names of the columns (column_1, column_2,..., column_n) of the table from which we want to retrieve the results. After the columns, we provide the targeted table name (the table from where the result is to be fetched). At last, we can provide some conditions on certain columns using the WHERE clause.

We can specify criteria on single and multiple columns (like column_1_criteria, column_2_criteria,..., column_n_criteria). We can also provide expressions as the grouping criteria.

Usage of Group By Multiple Columns

Let us discuss some of the usage and benefits of using group by multiple-column technique:

  • We can use the group by multiple-column technique to group multiple records into a single record.
  • All the records with the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple-column technique.
  • The group by multiple columns is used to get summarized data from a database's table(s).
  • The group by multiple columns is often used to generate queries for reports.

Examples

Now let us take some examples of groups by multiple columns to understand the topic better.

Group by Two Columns and Find Average

Suppose we are working on a company's database. We have two tables, namely employee and department. The employee table has three columns, namely employee_ID, salary, department_id. The department table has two columns, namely department_id and department_name. Let us try to find the average salary of employees in each department.

employee table:

employee_IDsalarydepartment_ID
100240001
101170001
103100001
10490002
105110002
106160003

department table:

department_IDdepartment_name
1Accounts
1Marketing
2IT

Query:

Output:

department_IDdepartment_nameaverage_salary
1Accounts17000
2Marketing10000
3IT16000

In the above query, we have joined the department and employee table and selected the department id, department name, and average salary. The average salary is accounted for by grouping employees based on their department.

Group by Two Columns and Find Multiple Stats

We have seen a grouping of employees by one column. Let us now learn how to group by multiple columns.

Let us consider the same department and employee table. The employee table has four columns, namely employee_ID, salary, department_id, and job_id. The department table has two columns, namely department_id and department_name. We have another table named job, which stores the job_id and job name.

Let us try to group by employee id and employee id.

employee table:

employee_IDsalarydepartment_IDjob_id
1002400011
1011700011
1031000011
104900023
1051100023
1061600032

department table:

department_IDdepartment_name
1Accounts
2IT
3Marketing

job table:

job_IDjob_title
1Accountant
2Manager
3HR

Query:

Output:

department_IDdepartment_namejob_IDCOUNT(e.employee_ID)
1Accounts13
2IT32
3Marketing21

The output (as above) is generated using the join operation of the three tables. After the join operation, the GROUP BY condition is added. We have also added the count aggregate function on the employee ID column.

Conclusion

  • To arrange similar (identical) data into groups, we use SQL group by clause. The GROUP BY clause is used along with some aggregate functions to group columns with the same values in different rows.
  • The group by multiple columns technique retrieves grouped column values from one or more database tables by considering more than one column as grouping criteria.
  • All the records with the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple-column technique.
  • The group by multiple columns is used to get summarized data from a database's table(s). The group by multiple columns is often used to generate queries for reports.