This post is part of the series on summarizing data but it gives us a more simple example to work with. This way we can be sure to understand how the Group By clause works. This post discusses the group by clause but does not discuss the having clause.

Below is the table we are using in this query in SQL Server. The table’s name is GroupBySum.

Here is the SQL query.

select [Type], [SubType], sum(Amount) as TotalAmount
from GroupBySum
group by [Type], [SubType]

The group by contains all of the columns that are not part of the math that we are performing (sum in this case). We have given the Amount column a name: TotalAmount. Here is the result.

Additional Columns

Normally in the real world you will have additional columns in your table. In this example there is a column called SpecificData. This could be anything, such as a person’s name or some sort of code number. It so happens in our case that each element of data is different. Here is the table GroupBySumTwo.

Below is a query that does not work. We get an error from SSMS when we try to run it.

select [Type], [SubType], [SpecificData], sum([Amount]) as TotalAmount
from [dbo].[GroupBySumTwo]
group by [Type], [SubType]

Here is the error message.

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.GroupBySumTwo.SpecificData' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The fix is easy. Simply omit the column name from the select statement.

select [Type], [SubType], sum([Amount]) as TotalAmount
from [dbo].[GroupBySumTwo]
group by [Type], [SubType]

Here is the result.

More Than One Aggregate Function

You can have more than one aggregate function. Here is a new table.

Here is a query.

select [Type], [SubType], sum([Amount]) as TotalAmount, avg([Quantity]) as AvgQuantity
from [dbo].[GroupBySumThree]
group by [Type], [SubType]

Here is the result.

Group By Only One Column

You can group by as many columns as you want. We have two columns in our example that we have been grouping on. Here we will just group on the one column: Type.

select [Type], sum([Amount]) as TotalAmount, avg([Quantity]) as AvgQuantity
from [dbo].[GroupBySumThree]
group by [Type]

Aggregate Function or Group By Clause

Here is a query that causes an error. Everything in the select part of the query must be either part of the group by or in an aggregate function

select [Type], [SubType], sum([Amount]) as TotalAmount, avg([Quantity]) as AvgQuantity
from [dbo].[GroupBySumThree]
group by [Type]

Here is the error that SSMS gives.

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.GroupBySumThree.SubType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Unclear

The query below works but it might be confusing to some. We are grouping by Type and SubType but we are only showing Type in the select part. What you include in the group by clause does not need to be included in the select clause.

select [Type],  sum([Amount]) as TotalAmount, avg([Quantity]) as AvgQuantity
from [dbo].[GroupBySumThree]
group by [Type], [SubType]

Here is the result of the above query in SSMS.