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. And the aggregated or the grouping columns can be one or more columns. In order to filter groups or the grouped rows, we have to use the having clause.

For instance, If you want to find out the Total number of Sales by region or country, use SQL Server Group By Clause to categorize the Sales table by region or country. Then we can use the aggregate function SUM with to calculate the total.

SQL GROUP BY Clause Syntax

The SQL Group By Clause syntax in the SELECT Statement is

SELECT [Column1], [Column2]...[ColumnN],
       Aggregate Function (Expression | Column_Name) 
FROM [Source]
WHERE [Conditions] -- Optional
GROUP BY [Column1], [Column2]...[ColumnN]
ORDER BY Cols

From the above SQL group by clause syntax, it 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 MIN, AVG, MAX, and COUNT. A Source can be one or more tables, and use Joins to join multiple tables.

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

If you want to display the aggregated information to the end-user, then use this clause. This article shows the use SQL Server Group By clause to categorize the data and aggregate while extracting it using the SELECT statement. We use the below data to explain this and the following example explains how this clause works in grouping column.

Customers Table

SQL Group By Count Single Column

In this example, we use a one column in this group Clause. The following query will count the total employee ids in an education category.

SELECT Education, 
	COUNT([EmpID]) AS [Total IDs]
FROM [Customer]
GROUP BY Education
SQL Group By Count Single Column

Group By Multiple Columns

Using more than one Column or multiple such as Education, and occupation in this transact SQL group by clause query.

SELECT Education, 
	[Occupation],
	COUNT([EmpID]) AS [Total IDs]
FROM [Customer]
GROUP BY Education, Occupation
SQL Group By Multiple Columns

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

SQL Group By Sum Function Example

Here, we use the Sum function in a single column. The following aggregate transact structured query language will calculate the total income of each department.

SELECT [Occupation],
	SUM(YearlyIncome) AS [Total Income]
FROM [Customer]
GROUP BY Occupation
SQL Group By Sum

Using SQL Group by Sum function and Multiple Columns example.

SELECT [Occupation],
	Education,
	SUM(YearlyIncome) AS [Total Income]
FROM [Customer]
GROUP BY Occupation, Education
Group By Sum and Multiple Columns

We can also use the Where Clause along with this one.

SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS [Total Income]
  FROM [Customer]
  WHERE [Occupation] <> 'Skilled Manual'
  GROUP BY [Occupation], [Education]

You can also use SQL GROUP BY Order By Clause and where clause along with this one. By using the order by clause, you can sort the records by descending or ascending order. The following statement sort customers by income in descending order.

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
Group By Order By Where Clause

Group By Aggregate Functions

In this example, We are going to use SUM, AVG, MIN and MAX aggregate function in a single SELECT Statement along with Order By Clause.

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]

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

Group By Top Clause

In the following sql statement, it groups rows by combine the Occupation and education and the Top Clause to select the first 6 records.

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

It is same as the above query but we used the below two lines of order by and having clause to it. This statement will combine the Customers by Occupation and Education. Next, Having Clause will return the result whose sum of Yearly Income is greater than 60000.

HAVING SUM([YearlyIncome]) > 60000
ORDER BY SUM(YearlyIncome) DESC
Group By Having Sum Example

SQL Group By Clause Complex example

Combining all the possibilities such as where, having, order by, sum that you can use along with this clause.

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
Group By Real Time query

Comments are closed.