SQL GROUP BY Clause

What is the SQL Group by Clause?

The SQL GROUP BY clause is used to group the result set based on common value present in the result set. The grouping of the result set is done after all records are retrieved from tables.

The GROUP BY clause can be used with the conjunction of SELECT query to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to the individual groups.
The main importance of the GROUP BY clause is to summarize data from the database, so these commands are frequently used in the reporting queries. Only one result is returned after executing the GROUP BY clause & these queries are called grouped queries.

You can use the WHERE clause along with the GROUP BY clause, but WHERE condition is used to limit the records from the tables & GROUP BY clause is used to group the retrieved result set based on column name specified. If you want to filter the grouped result set then we can use the HAVING clause. This is used after the GROUP BY clause.

SQL GROUP BY Clause Syntax:

SELECT * from Table_Name
GROUP BY Column_Name1,
[Column_Name2,….]
[HAVING CONDITION];

• [SELECT * from Table_Name] – This is the regular SELECT command to get the records from the database.
• GROUP BY Column_Name1 – This is clause is used to group the result set based column name specified. Here you can specify one or more column names.
• [Column_Name2,….] – This is optional. In the GROUP BY clause, one column name is mandatory on which you have to group the result set. If you specify more than one column name then result set the first group on first column value & then next column(s).
• [HAVING CONDITION] – This Clause used to filter out the result data set based on “CONDITION”. You can specify any condition using the HAVING clause.

In this tutorial, we have created a sample Employees table on which we are executing the different SQL GROUP BY queries,

Using GROUP BY with Aggregate Functions:

The use of AGGREGATE functions is very much powerful when we use them along with the GROUP BY clause. In the SQL GROUP BY queries, the most of time GROUP BY clauses are used with AGGREGATE functions.

Here we are going to see different GROUP BY Clause example with different combinations of the AGGREGATE Functions:

1. SQL GROUP BY with SUM() function
2. SQL GROUP BY with COUNT() function
3. SQL GROUP BY with COUNT() and SUM()
4. SQL GROUP BY on more than one column
5. SQL GROUP BY with WHERE clause
6. SQL GROUP BY with HAVING clause

1) SQL GROUP BY with SUM() function:

Suppose the user wants to get the sum of Salary paid to each department, so in the Employees table, we have a column called DEPARTMENT_ID & SALARY columns. So we can group the result set using DEPARTMENT_ID & we will use the SUM Aggregate function to get some of the salary by each department.

SELECT DEPARTMENT_ID,
SUM(SALARY) as Total_Salary
FROM Employees
GROUP BY DEPARTMENT_ID;

Following is the result upon executing the above SQL GROUP BY query,

2) SQL GROUP BY with COUNT() function:

Suppose the user wants to get the number of employees in each department, so in the Employees table we have a column called DEPARTMENT_ID. So we can group the result set using DEPARTMENT_ID & we will use the COUNT Aggregate function to get a number of employees by each department.

SELECT DEPARTMENT_ID,
COUNT(*) as Number_Of_Emloyees
FROM Employees
GROUP BY DEPARTMENT_ID;

Following is the result upon executing the above SQL GROUP BY query,

3) SQL GROUP BY with COUNT() and SUM() function:

Suppose the user wants to get the number of employees in each department & sum of Salary paid to each department, so in the Employees table we have a column called DEPARTMENT_ID and SALARY. So we can group the result set using DEPARTMENT_ID & we will use COUNT() & SUM() Aggregate functions. COUNT() is used to get the number of employees by each department & SUM() is used to get the sum of

SELECT DEPARTMENT_ID,
COUNT(*) as Number_Of_Emloyees,
SUM(SALARY)
FROM Employees
GROUP BY DEPARTMENT_ID;

Following is the result upon executing the above SQL GROUP BY query,

4) SQL GROUP BY on more than one column:

Group by Department id, Job Id & & SUM of salary with department

Suppose the user wants to get the number of employees in each department & sum of Salary paid to each department by Job ID, so in the Employees table we have a column called DEPARTMENT_ID, SALARY, and Job_ID. So here first we can group the result set using DEPARTMENT_ID & then by JOB_ID. Also, we will use the SUM() Aggregate function to get the sum of salary by each Job_id within each department.

SELECT DEPARTMENT_ID,
JOB_ID,
SUM(SALARY)
FROM Employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Following is the result upon executing the above SQL GROUP BY query,

5) SQL GROUP BY with WHERE clause:

Suppose a user wants to get the sum of Salary paid to DEPARTMENT_ID = 50, so in the Employees table we have a column called DEPARTMENT_ID and SALARY. So here first we will use the WHERE condition on DEPARTMENT_ID = 50, this fitter the result set & returns only records from employee table related to DEPARTMENT_ID = 50. Next to the WHERE clause, we can group the result set using DEPARTMENT_ID.

SELECT DEPARTMENT_ID,
SUM(SALARY)
FROM Employees
WHERE DEPARTMENT_ID = 50
GROUP BY DEPARTMENT_ID;

Following is the result upon executing the above SQL GROUP BY query,

6) SQL GROUP BY with HAVING clause:

Group by Department id & count of an employee with department & HAVING count > 1

Suppose the user wants to get the number of employees from each department where at least two employees present in department. So we can group the result set using DEPARTMENT_ID & we will use COUNT()Aggregate function to get the number of employees by each department.

Here we will use the HAVING clause to get the employee count > 1

SELECT DEPARTMENT_ID,
COUNT(*) as Number_Of_Emloyees
FROM Employees
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 1;

Following is the result upon executing the above SQL GROUP BY query,

Conclusion on SQL GROUP BY Clause:

• The SQL GROUP BY clause is used to divide the result set into smaller groups.
• The grouping can happen after retrieves the rows from a table.
• In the GROUP BY Clause, the grouping is done using the same values present in the result set.
• The GROUP BY Clause is used with the conjunction of the SQL SELECT query.
• The WHERE clause is used to retrieve rows based on a certain condition, but it cannot be applied to the grouped results.
• The HAVING clause is used to filter the result set of the GROUP BY clause. This is used after the GROUP BY clause.

Over to you on SQL GROUP BY Clause:
Have you worked on Database testing? If yes, I would like all of you to please join this discussion and add more valuable points to it. If not please ask questions in comment below, also don’t forgot to subscribe here to get software testing articles in your inbox. Click here to subscribe to your email address or enter your email id below.

Enter your email address:

 

Check email in your inbox for confirmation to get latest updates Software Testing for free.

If you enjoy reading this article please make sure to share it with your friends. Please leave your questions/tips/suggestions in the comment section below and I’ll try to answer as many as I can.

Happy Testing!!!