SQL GROUP BY

What is GROUP BY?

GROUP BY is a SQL query clause used to group data rows based on one or more columns in a table. The query returns aggregated data instead of individual rows when using GROUP BY. The GROUP BY clause is often combined with aggregate functions such as SUM, AVG, COUNT, MAX, and MIN to calculate statistics or summarize data.

It's important to note that only the columns listed in the GROUP BY clause and aggregate functions can be included in the SELECT statement when using GROUP BY. Any other columns not included in the GROUP BY clause or an aggregate function will result in an error.

GROUP BY Syntax

Sample Table

Student Table:

Suppose we have a table name student containing details of all students in the CS department. So we will use this table's sample data to group students based on their departments.

Emp Table:

This table contains details of employees' roles in a particular company and has data fields as EmpID, Role, Dept.

SQL GROUP BY Examples

GROUP BY with a Single Column

When we are grouping the Table using a single column, the output has all the rows with the same value of that particular column.

Let us understand the scenario in which we had to get all maximum marks from each section in CS department.

Syntax:

Example:

Let's take the above example. So for that, we will group all the entries by sections from which they belong and will apply max function on marks to get maximum marks from each section.

Output:

We will simply get the maximum marks of students section-wise along with their section names.

GROUP BY with Multiple Columns

When we are grouping the Table using multiple columns, the output has all the rows with the same value of both of those columns.

Let us understand grouping for multiple columns by example in which we have to get the count of all employees grouped by Role and Dept.

Syntax:

Example:

We will group all the entries by dept and role of the employees and will apply the count function on the entries to get a count by dept and role.

Output:

We will simply get an output table with a count corresponding to the dept and role of the employees.

GROUP BY in SQL with Join Clause

GROUP BY can be used in SQL queries with a JOIN clause to group data from multiple tables based on some common columns. When using GROUP BY with JOIN, the resulting data is grouped based on the columns specified in the GROUP BY clause across all tables in the JOIN statement.

For example, let's say we have two tables, employees and departments, with the following columns:

The employees table has the following attributes:

• employee_id
• first_name
• last_name
• email
• department_id

The departments table has the following attributes:

• department_id
• department_name
• manager_name

To join these two tables and group the data by department name, we can use the following SQL query:

This query will join the employees and departments tables on the department_id column. The resulting data will be grouped using the department name attribute. The SELECT statement will obtain the department name and the count of employees in each department using the COUNT() function and the employee_id column from the employees table.

GROUP BY with Having Clause

We can use the HAVING clause to specify which groups will be included in the final result set. We also can't utilize aggregate functions like SUM(), COUNT(), and so on with the WHERE clause, as WHERE clause is used to place conditions on columns. So If we want to use aggregate functions on a group of data(group by), we must use the HAVING clause.

Syntax:

Example:

We will take the above student table and will apply the filter condition for getting the maximum marks from each section and max marks should be more than 90.

Output:

We got the values of the section having the maximum marks greater value 90.