SQL GROUP BY Clause

The SQL Server GROUP BY Clause returns the aggregated data by grouping one or more columns and performing the aggregated functions on the remaining columns. And the aggregated columns can be one or more columns. In order to filter the SQL Server grouped rows, we have to use the having clause.

For instance, If you want to determine 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 SQL Server 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 SQL Server group by clause syntax allows us to SELECT the number of columns from the tables, which may be one or more. We can use 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, only the SQL Server Group By SELECT Statement returns the records. All Selected Columns not part of the Aggregate Functions should place after this clause.

If you want to display the aggregated information to the end user, 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.

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

Multiple Columns

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

SELECT Education, 
	[Occupation],
	COUNT([EmpID]) AS [Total IDs]
FROM [Customer]
GROUP BY Education, Occupation

If you observe the above SQL Server screenshot, column values are repeating, although we combined the Occupation. Here, we used two columns (Occupation and Education) in this statement—people who studied for 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

Using the Sum function and Multiple Columns example.

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

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]

You can also use SQL Server GROUP BY Order By and where clause along with this Sum function. 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

Sql Server group by Order By Where Clause

SQL Server Group by clause Aggregate Functions

In this example, we will use SUM, AVG, MIN, and MAX aggregate functions in a single SELECT Statement and 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]

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

Top Clause

In the following SQL Server Group By Top Clause 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

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

Complex example

Combining all the possibilities, such as the where, having, order by, and sum that you can use along with this SQL Server Group By 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