Software Testing Models





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 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 individual group.
The main importance of GROUP BY clause is to summarizing of data from database, so these commands are frquently used in the reporting queries. Only one result is returned after the executing the GROUP BY clause & these queries are called as grouped queries.

You can use the WHERE clause along with GROUP BY clause, but WHERE condition is used to limit the records from the tables & GROUP BY clause is use 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 regular SELECT command to get the records from 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 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 HAVING clause.

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

SQL GROUP BY Clause

Using GROUP BY with Aggregate Functions:

The use of AGGREGATE functions is very much powerful when we use then along with GROUP BY clause. In the SQL GROUP BY queries, the most of times 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 user want to get the sum of Salary paid to each department, so in the Employees table we have 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 sum of 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,

SQL GROUP BY with Aggregate Function

2) SQL GROUP BY with COUNT() function:

Suppose user wants to get the number employees in each department, so in the Employees table we have column called DEPARTMENT_ID. So we can group the result set using DEPARTMENT_ID & we will use the COUNT Aggregate function to get 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,

SQL GROUP BY with Count Function

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

Suppose user wants to get the number employees in each department & sum of Salary paid to each department, so in the Employees table we have 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,

SQL GROUP BY with Count and Sum Function

4) SQL GROUP BY on more than one column:

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

Suppose user wants to get the number employees in each department & sum of Salary paid to each department by Job ID, so in the Employees table we have 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,

SQL GROUP BY with more than one column

5) SQL GROUP BY with WHERE clause:

Suppose user wants to get the sum of Salary paid to DEPARTMENT_ID = 50, so in the Employees table we have column called DEPARTMENT_ID and SALARY. So here first we will use the WHERE condition on DEPARTMENT_ID = 50, this fitters the result set & returns only records from employee table related to DEPARTMENT_ID = 50. Next to 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,

SQL GROUP BY with WHERE clause

6) SQL GROUP BY with HAVING clause:

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

Suppose user wants to get the number of employees from each department where at least two employee 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 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,

SQL GROUP BY with HAVING clause

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 same values present in the result set.
  • The GROUP BY Clause is used with the conjunction of SQL SELECT query.
  • The WHERE clause is used to retrieve rows based on a certain condition, but it cannot be applied to grouped result.
  • The HAVING clause is used to filter the result set of GROUP BY clause. This is used after 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 with your email address or enter you 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!!!

1 comment to SQL GROUP BY Clause

Leave a Reply

  

  

  


*

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>