SQL GROUP BY Clause

The SQL GROUP BY Clause returns the aggregated data by grouping one or more columns and performs the aggregated functions on the remaining columns. For instance, If you want to find out the Total number of Sales by region or country, use SQL Group By Clause to group the Sales table by region or country. Then we can use the aggregate function SUM with Group by to calculate the total.

SQL GROUP BY Clause Syntax

The SQL Server Group By Clause syntax in the SELECT Statement is

-- SQL Server Group By Clause Syntax
SELECT [Column1], [Column2]...[ColumnN],
       Aggregate Function (Expression | Column_Name) 
FROM [Source]
WHERE [Conditions] -- Optional
GROUP BY [Column1], [Column2]...[ColumnN]
ORDER BY Columns

From the above SQL group by clause syntax, columns allow us to SELECT the number of columns from the tables, and it may be one or more. We can use any aggregate functions such as SUM, MIN, AVG, MAX, and COUNT. A Source can be one or more, and use Joins to join multiple tables. 

Conditions: Provide filters or conditions. If the condition is TRUE, then only the SELECT Statement returns the records. Group By: All the Selected Columns which are not part of the Aggregate Functions should place after this Group by clause.

If you want to display the aggregated information to the end-user, then use the Group by clause. This article shows the use SQL Group By clause to group the data and aggregate while extracting it using the SELECT statement. We use the below data to explain the Group By clause.

SQL Group By Clause 1

SQL Group By Count Single Column

In this example, we use a single column in the Group By Clause. The following group by count query will count the total employee ids in an education group.

USE [SQL Tutorial]
GO
SELECT Education, 
	COUNT([EmpID]) AS [Total IDs]
FROM [Customer]
GROUP BY Education
SQL Group By Clause 2

SQL Group By Multiple Columns

Using Multiple Columns such as Education, and occupation in the Group By Clause.

USE [SQL Tutorial]
GO
SELECT Education, 
	[Occupation],
	COUNT([EmpID]) AS [Total IDs]
FROM [Customer]
GROUP BY Education, Occupation
SQL Group By Clause 3

If you observe the above SQL Server screenshot, Although we grouped by the Occupation, column values are repeating. It is because Here we used two columns (Occupation and Education) in the group by statement. People who studied bachelor’s degrees are working in Management, and few are working in the Professional.

SQL Group By Sum Example 1

Here, we use the Sum function in a single column in the Group By Clause. The following group by sum query will calculate the total income of each department.

-- SQL Server Group By Sum Example
USE [SQL Tutorial]
GO
SELECT [Occupation],
	SUM(YearlyIncome) AS [Total Income]
FROM [Customer]
GROUP BY Occupation
SQL Group By Clause 4

SQL Group By Sum Example 2

Using Sum function and Multiple Columns with Group By Clause.

-- SQL Server Group By Sum Example
USE [SQL Tutorial]
GO
SELECT [Occupation],
	Education,
	SUM(YearlyIncome) AS [Total Income]
FROM [Customer]
GROUP BY Occupation, Education
SQL Group By Clause 5

SQL Group By Where Clause

We can also use the Where Clause along with the Group By clause.

-- SQL Server Group By Example
SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS [Total Income]
  FROM [Customer]
  WHERE [Occupation] <> 'Skilled Manual'
  GROUP BY [Occupation], [Education]
SQL Group By Clause 6

SQL Group By Order By Example

You can also use Order By Clause along with the Group By.

-- SQL Server Group By Example
SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS [Total Income]
	  ,SUM(Sales) AS [Total Sales]
  FROM [Customer]
  WHERE [Occupation] <> 'Skilled Manual'
  GROUP BY [Occupation], [Education]
  ORDER BY SUM([YearlyIncome]) DESC
SQL Group By Clause 7

SQL Group By Aggregate Functions

In this group by clause example, We are going to use SUM, AVG, MIN and MAX aggregate function in a single SELECT Statement.

-- SQL Server Group By Example
SELECT [Occupation]
      ,SUM([YearlyIncome]) AS [Total Income]
      ,AVG([YearlyIncome]) AS [Average Income]
      ,MIN([YearlyIncome]) AS [Minimum Salery]
      ,MAX([YearlyIncome]) AS [Maximum Salery]
  FROM [Customer]
  GROUP BY [Occupation]
  ORDER BY  [Occupation]
SQL Group By Clause 8

NOTE: If you forgot the ALIAS Column Names then [No Column Name] will be displayed as header text.

SQL Group By Top Clause

Here, Group By clause is used to group by Occupation and education and the Top Clause to select the first 6 records.

-- SQL Server Group By Example

SELECT TOP 6 [Occupation],
	Education,
	SUM(YearlyIncome) AS [Total Income],
	SUM(Sales) AS [Total Sales]
FROM [Customer]
GROUP BY Occupation, Education
ORDER BY SUM(YearlyIncome) DESC
SQL Group By Clause 9

SQL Group By Having Example

The Group By statement will Group the Customers by Occupation and Education. Next, Having Clause will return the group result whose sum of Yearly Income is greater than 60000.

-- SQL Server Group By Example
SELECT [Occupation],
	Education,
	SUM(YearlyIncome) AS [Total Income],
	SUM(Sales) AS [Total Sales]
FROM [Customer]
GROUP BY Occupation, Education
HAVING SUM([YearlyIncome]) > 60000
ORDER BY SUM(YearlyIncome) DESC
SQL Group By Clause 10

Complex Group By example

Combining all the possibilities that you can use along with Group By clause.

USE [SQL Tutorial]
GO
SELECT [Occupation],
	Education,
	SUM(YearlyIncome) AS [Total Income],
	SUM(Sales) AS [Total Sales]
FROM [Customer]
WHERE [Education] <> 'Partial High School'
GROUP BY Occupation, Education
HAVING SUM([YearlyIncome]) > 60000
ORDER BY Occupation, Education
SQL Group By Clause 11

Comments are closed.