Group by in SQL Server with example

A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

The GROUP BY clause allows you to arrange the rows of a query in groups. The groups are determined by the columns that you specify in the GROUP BY clause.

Let's understand this by simple example:

Example: Group by on Single Column

Table Name : Order

CustomerId	ProductId	Quantity	Price
1	2	5	100
1	3	2	200
1	4	1	500
2	1	4	50
3	5	6	700

When grouping by a specific column, only unique values of this column are returned.

SELECT customerId
FROM orders
GROUP BY customerId;

Output:

CustomerId

1

2

3

Aggregate functions like count() apply to each group and not to the complete table:

SELECT customerId,
COUNT(productId) as numberOfProducts,
SUM(price) as totalPrice
FROM orders
GROUP BY customerId;

OutPut:

customerId	numberOfProducts	totalPrice
1	3	800
2	1	50
3	1	700

Example: Group by multiple column.

One might want to GROUP BY more than one column

Let's see the below example. 

declare @temp table(age int, name varchar(15))
insert into @temp
select 18, 'matt' union all
select 21, 'matt' union all
select 21, 'matt' union all
select 18, 'luke' union all
select 18, 'luke' union all
select 21, 'luke' union all
select 18, 'luke' union all
select 21, 'luke'

SQL Code:

SELECT Age, Name, count(1) count
FROM @temp
GROUP BY Age, Name

Output:

Age	Name	count
18	luke	3
21	luke	2
18	matt	1
21	matt	2