The SQL GROUP BY Clause is used to return the aggregated data by grouping one or more columns and performs the aggregated functions on the remaining columns.
TIP: If you want to display the aggregated information to the end-user then you have to use the SQL Server Group by statement.
For instance, If you want to find out the Total number of Sales by region or country then you can simply use Group By statement to group the Sales table by region or country and then we can use the aggregate function SUM to calculate the total.
SQL GROUP BY Clause Syntax
In SQL Server, the syntax of the SELECT Statement with Group By Clause can be written as:
-- 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]
- Columns: It allows us to choose the number of columns from the tables. It may be One or more.
- Aggregate Functions: We can use any of the aggregate functions here. SUM, MIN, AVG, MAX and COUNT are the functions we can use.
- Source: One or more tables present in the Database. JOINs are used to join multiple table.
- Conditions: Here we have to provide the filters or conditions. If the condition is TRUE then only SELECT Statement will return the records.
- Group By: All the Selected Columns which are not part of the Aggregate Functions should place after this Group by clause.
In this article we will show you, How to write SQL Group By Clause to group the data and aggregate while extracting it using SELECT statement. We are going to use the below shown data to explain the Group By Statement in SQL Server with example.
Sql Group By using Single Column
In this example we are going to use single column in the Group By Clause. The following query will calculate the total income of each department.
-- SQL Server Group By Clause Example SELECT [Occupation] ,SUM([YearlyIncome]) AS [Department Income] FROM [Customer] GROUP BY [Occupation]
Using Multiple Columns in SQL Group By
In this example we are going to use Multiple Columns in the Group By Clause.
-- SQL Server Group By Clause Example SELECT [Occupation] ,[Education] ,SUM([YearlyIncome]) AS [Total Income] FROM [Customer] GROUP BY [Occupation], [Education]
If you observe the above screenshot, Although we grouped by the Occupation, column values are repeating. This is because, Here we used two columns (Occupation and Education) in the group by statement. People who studied Bachelors degree are working in the Management and few are working in the Professional.
Using Where Clause in SQL Group By
We can also use the Where Clause along with the Group By condition.
-- SQL Server Group By Clause Example SELECT [Occupation] ,[Education] ,SUM([YearlyIncome]) AS [Total Income] FROM [Customer] WHERE [Occupation] <> 'Skilled Manual' GROUP BY [Occupation], [Education]
Sql Group By – Use Aggregate Functions
In this example, We are going to use SUM, AVG, MIN and MAX aggregate function in single SELECT Statement.
-- SQL Server Group By Clause 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]
NOTE: If you forgot the ALIAS Column Names then [No Column Name] will be displayed as header text. Please provide the valid columns names to avoid the confusion.
Thank You for Visiting Our Blog