SQL GROUP BY Clause

The SQL Server 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 columns can be one or more columns. In order to filter 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 to calculate the total.

SQL GROUP BY Clause Syntax

The 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

The above group by clause syntax allows us to SELECT the number of columns from the tables, which 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 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 of SQL Server Group By clause to categorize and aggregate the data while extracting it using the SELECT statement. We use the below data to explain this, and the following example explains how this works in the table column.

Customers Table

SQL Group By Count Single Column

In this example, we use one column. 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

Multiple Columns

Using more than one column or multiple such as Education and occupation in this transact 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 work in Management, while others work as professionals.

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 the Sum function and Multiple Columns example.

SELECT [Occupation],
	Education,
	SUM(YearlyIncome) AS [Total Income]
FROM [Customer]
GROUP BY Occupation, Education
Sum Multiple columns

We can also use the Where along with this one.

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

You can also use GROUP BY Order By and where clause along with this Sum function. By using the order by, you can sort the records in descending or ascending order. The following statement sorts 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
Order By Where

Aggregate Functions

In this example, we will use SUM, AVG, MIN, and MAX aggregate functions in a single SELECT Statement along with Order By.

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]
Aggregate Functions

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

Top Clause

In the following statement, groups row by combining 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 the 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, the Having will return the result whose sum of Yearly Income is greater than 60000.

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

Complex example

Combining all the possibilities such as the where, having, order by, and 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
Real Time query

Comments are closed.