Home > T - SQL > GROUP BY Clause (SQL Server 2005 Vs 2008)

GROUP BY Clause (SQL Server 2005 Vs 2008)

For proper analyzing of data, sometimes we need to group them under certain categories.  These categories are defined under some constraints over the data. For this GROUP BY Clause is very useful.

GROUP BY Clause is used with SELECT Command, and specifies the groups into which output rows are to be placed by the values of one or more columns or expressions.  And if aggregated functions are used in the SELECT statements, then GROUP BY clause computes a summary value for each group.

GROUP BY Clause in SQL Server 2005

SYNTAX:

[ GROUP BY [ ALL ] group_by_expression [ ,...n]
    [ WITH { CUBE | ROLLUP } ]

Arguments:  

[ALL] It will group by considering all the records in the group_by_expression columns even using the rows containing NULL values or the rows that are not considered in WHERE clause. You cannot specify ALL with the CUBE or ROLLUP operators.
Note: GROUP BY ALL is not supported in SQL Server Compact 3.5
group_by_expression is an expression on which grouping is performed. The group_by_expression parameter can be a column or a non-aggregate expression that refer a column. A column alias defined in the select list cannot be used as a grouping column. Aggregate expressions cannot be specified in a group_by_expression. Columns of type text, ntext, and image cannot be used in group_by_expression.
CUBE This will show additional summary rows along with the usual rows. It will show every possible combination of group and subgroup in the result set.
ROLLUP In addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy depends on the order in which the grouping columns are specified and the result set may be changed by changing the order of the grouping columns.



Note: Distinct aggregates, for example, AVG(DISTINCT column_name), COUNT(DISTINCT column_name), and SUM(DISTINCT column_name) are not supported when you use CUBE or ROLLUP. If these are used, the Microsoft SQL Server 2005 Database Engine returns an error message and cancels the query.

Actual Syntax

SELECT select_list
       [INTO new_table_]
FROM table_source
       [WHERE search_condition]
[GROUP BY group_by_expression]
       [HAVING search_condition]
       [ORDER BY order_expression [ASC | DESC] ]
  •  Effect of [ALL] keyword in GROUP BY clause:

Suppose we have a table called dbo.Car_Info having the following structure:

Column_name

Type

CarCompany

varchar

CarBodyType

varchar

CarName

varchar

EngineType

varchar

 And the data associated to it are:

CarCompany

CarBodyType

CarName

EngineType

Maruti

small

Maruti-800

petrol

Maruti

small

Waganor Duo

petrol

Honda

sedan

City

petrol

TATA

small

indica

diesel

Mahindra

SUV

Scorpio

diesel

TATA

SUV

Sumo

diesel

Maruti

sedan

SX4

petrol

Maruti

sedan

Swift-Dzire

diesel

TATA

small

Nano

petrol

Now let see the effect of [ALL] keyword on this table.

Problem: Here we have to find out the total number of cars available under each Car Body type.

Case 1: (without ALL keyword)

select CarBodyType, count(CarBodyType) as CountTotal
       from dbo.Car_Info
where CarBodyType IN ('small','sedan')
        group by CarBodyType

Output:

CarBodyType

CountTotal

sedan

3

small

4

Case 2: (with ALL keyword)

select CarBodyType, count(CarBodyType) as CountTotal
from dbo.Car_Info
where CarBodyType IN ('small','sedan')
group by ALL CarBodyType

 Output:

CarBodyType

CountTotal

sedan

3

small

4

SUV

0

Note: It’s better to avoid using [ALL] keyword while developing applications as this keyword will be removed in the future versions of SQL Server.

  • Query with CUBE keyword:

The last row has the summation of total count. Shows all possible aggregations of the table.

GROUPING ( column_name ):   Returns [true] or [1] when the row is affected by either the CUBE or ROLLUP operator.

Example 1: To find the total number of cars under each Engine type categorized by company name.

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  EngineType, CarCompany  with CUBE

Output:

EngineType

CarCompany

CountTotal

diesel

Mahindra

1

diesel

Maruti

1

diesel

TATA

2

diesel

ALL

4

petrol

Honda

1

petrol

Maruti

3

petrol

TATA

1

petrol

ALL

5

ALL

ALL

9

ALL

Honda

1

ALL

Mahindra

1

ALL

Maruti

4

ALL

TATA

3

Example 2: To find the total number of cars under each Engine type categorized by company name with body type “small”

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
where CarBodyType ='small'
group by  EngineType, CarCompany  with CUBE

Output:

EngineType

CarCompany

CountTotal

diesel

TATA

1

diesel

ALL

1

petrol

Maruti

2

petrol

TATA

1

petrol

ALL

3

ALL

ALL

4

ALL

Maruti

2

ALL

TATA

2

Effect of changing the columns in GROUP BY clause

Example 1: To find the total number of cars under each Company Name categorized by Engine Type. 

select
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
count(*) as CountTotal
from dbo.Car_Info
group by CarCompany, EngineType   with CUBE

Output:

CarCompany

EngineType

CountTotal

Honda

petrol

1

Honda

ALL

1

Mahindra

diesel

1

Mahindra

ALL

1

Maruti

diesel

1

Maruti

petrol

3

Maruti

ALL

4

TATA

diesel

2

TATA

petrol

1

TATA

ALL

3

ALL

ALL

9

ALL

diesel

4

ALL

petrol

5

Example 2: To find the total number of cars under each Company Name categorized by Engine Type with body type “small”

select
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
count(*) as CountTotal
from dbo.Car_Info
where CarBodyType ='small'
group by CarCompany, EngineType   with CUBE
  • Query with ROLLUP keyword:

Useful in generating reports that contain subtotals and totals. It shows aggregates for a hierarchy of values in the selected columns.

Example 1: To find the total number of cars under each Engine type categorized by company name.

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  EngineType, CarCompany  with ROLLUP

 Output:

EngineType

CarCompany

CountTotal

diesel

Mahindra

1

diesel

Maruti

1

diesel

TATA

2

diesel

ALL

4

petrol

Honda

1

petrol

Maruti

3

petrol

TATA

1

petrol

ALL

5

ALL

ALL

9

Example 2: To find the total number of cars under each Engine type categorized by company name with body type “small”

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
where CarBodyType ='small'
group by  EngineType, CarCompany  with ROLLUP

Output:

EngineType

CarCompany

CountTotal

diesel

TATA

1

diesel

ALL

1

petrol

Maruti

2

petrol

TATA

1

petrol

ALL

3

ALL

ALL

4

Effect of changing the columns in GROUP BY clause

Example 1: To find the total number of cars under each Company Name categorized by Engine Type.

select
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
count(*) as CountTotal
from dbo.Car_Info
group by CarCompany, EngineType   with ROLLUP

 Output:

CarCompany

EngineType

CountTotal

Honda

petrol

1

Honda

ALL

1

Mahindra

diesel

1

Mahindra

ALL

1

Maruti

diesel

1

Maruti

petrol

3

Maruti

ALL

4

TATA

diesel

2

TATA

petrol

1

TATA

ALL

3

ALL

ALL

9

Example 2: To find the total number of cars under each Company Name categorized by Engine Type with body type “small”.

select
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
count(*) as CountTotal
from dbo.Car_Info
where CarBodyType ='small'
group by CarCompany, EngineType   with ROLLUP

Output:

CarCompany

EngineType

CountTotal

Maruti

petrol

2

Maruti

ALL

2

TATA

diesel

1

TATA

petrol

1

TATA

ALL

2

ALL

ALL

4



Note: Any NULL values in the grouping columns can be treated as another group only i.e. all null values are put into a single group. If a column is aggregated in a row, the value of the column is shown as NULL.

GROUP BY Clause in SQL Server 2008

Here in SQL Server 2008, GROUP BY clause comes with some additional set of keywords which ease the operation to a great extent.  Here the GROUP BY clause includes GROUPING SETS, CUBE, ROLLUP, WITH CUBE and WITH ROLLUP keywords.

There are some major enhancements in GROUP BY Clause in SQL Server 2008 like

  • Insertion of GROUPING SETS keywords
  • Change in ROLLUP and CUBE keywords (syntactical), although old ways are also working.
  • Addition of GROUPING_ID Function

SYNTAX:

GROUP BY
<simple group by item>
      | <column_expression>
      | <rollup spec>
      | <cube spec>
      | <grouping sets spec>
      | <grand total>

 Arguments:

<column_expression> : Any expression on which the grouping operation is performed.

  • ROLLUP ( ): Summery rows are included in the result set on a hierarchical basis. A row containing the grand total of all the rows is added at the end. Here the columns are rolled up from left to right and hence change in the order of the columns will affect the final result set.

Example: To find the total number of cars under each Engine type categorized by company name.

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  ROLLUP(EngineType, CarCompany)

Output:

EngineType

CarCompany

CountTotal

diesel

Mahindra

1

diesel

Maruti

1

diesel

TATA

2

diesel

ALL

4

petrol

Honda

1

petrol

Maruti

3

petrol

TATA

1

petrol

ALL

5

ALL

ALL

9

  • CUBE ( ) : Summery rows are added at each grouping level irrespective of the order of grouping columns i.e. it contains simple aggregate rows, the ROLLUP super-aggregated rows and cross-tabulation rows.

Example: To find the total number of cars under each Engine type categorized by company name. 

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  CUBE(EngineType, CarCompany)

Output:

EngineType

CarCompany

CountTotal

petrol

Honda

1

ALL

Honda

1

diesel

Mahindra

1

ALL

Mahindra

1

diesel

Maruti

1

petrol

Maruti

3

ALL

Maruti

4

diesel

TATA

2

petrol

TATA

1

ALL

TATA

3

ALL

ALL

9

diesel

ALL

4

petrol

ALL

5

  • GROUPING SETS ( ): This operator is a great enhancement in SQL Server 2008, and is very useful in serving many needs. Instead of grouping all the sets, it will show only those groups that are specified in the GROUPING SETS operator. Here, any combination of groping sets can be created. Here, () means a grand total row.

Example 1: To get the total number of cars under each group of the column specified. 

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  GROUPING SETS(EngineType, CarCompany,())

Output

EngineType

CarCompany

CountTotal

ALL

Honda

1

ALL

Mahindra

1

ALL

Maruti

4

ALL

TATA

3

ALL

ALL

9

diesel

ALL

4

petrol

ALL

5

Example 2: To get total number of cars grouped by both Engine type and Company name.

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  GROUPING SETS((EngineType, CarCompany),())

Output

EngineType

CarCompany

CountTotal

diesel

Mahindra

1

diesel

Maruti

1

diesel

TATA

2

petrol

Honda

1

petrol

Maruti

3

petrol

TATA

1

ALL

ALL

9

Example 3: Any number of subsets can be used including CUBE() and ROLLUP() inside GROUPING SETS() but it may lead to redundant rows.

select
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  GROUPING SETS((EngineType, CarCompany),ROLLUP(EngineType), CUBE(CarCompany),())

Output

EngineType

CarCompany

CountTotal

petrol

Honda

1

ALL

Honda

1

diesel

Mahindra

1

ALL

Mahindra

1

diesel

Maruti

1

petrol

Maruti

3

ALL

Maruti

4

diesel

TATA

2

petrol

TATA

1

ALL

TATA

3

ALL

ALL

9

diesel

ALL

4

petrol

ALL

5

ALL

ALL

9

ALL

ALL

9

Here the rows highlighted with yellow colors are redundant rows, one coming from ROLLUP(), second one coming from CUBE() and the third one from ().

Note: The GROUPING SETS logic can be achieved by using UNION ALL keyword.

Example:

The following statements are equivalent:

By Using GROUPING SETS

By Using UNION ALL

select

case

      When GROUPING(EngineType) = 1 then ‘ALL’

      else EngineType end as EngineType,

case

      when GROUPING(CarCompany) = 1 then ‘ALL’

      else CarCompany end as CarCompany,

count(*) as CountTotal

from dbo.Car_Info

group by  GROUPING SETS(EngineType, CarCompany, ())

select

‘ALL’ as EngineType, CarCompany, count(*) as CountTotal

from dbo.Car_Info

group by CarCompany

UNION ALL

Select

EngineType, ‘ALL’ as CarCompany,  count(*) as CountTotal

from dbo.Car_Info

group by EngineType

UNION ALL

select

‘ALL’ as EngineType, ‘ALL’ as CarCompany,  count(*) as CountTotal

 from dbo.Car_Info

Output:

Output:

EngineType

CarCompany

CountTotal

ALL

Honda

1

ALL

Mahindra

1

ALL

Maruti

4

ALL

TATA

3

ALL

ALL

9

diesel

ALL

4

petrol

ALL

5

 

EngineType

CarCompany

CountTotal

ALL

Honda

1

ALL

Mahindra

1

ALL

Maruti

4

ALL

TATA

3

diesel

ALL

4

petrol

ALL

5

ALL

ALL

9

 

  • GROUPING_ID() : It’s a new function added in SQL Server 2008 for GROUP BY clause and its main objective is to compute the level of grouping for a particular column. This can be used in SELECT, HAVING or ORDER BY clauses. Its return type is INT.

In earlier versions GROUPING() function was used to detect whether a column is Grouped or not, but here the level of grouping can also be detected.

Syntax:

GROUPING_ID ( <column_expression> [ ,...n ] )

 Example: To get the grouping ID at each level. 

select
GROUPING_ID(EngineType,CarCompany) as G_ID,
case
	When GROUPING(EngineType) = 1 then 'ALL'
	else EngineType end as EngineType,
case
	when GROUPING(CarCompany) = 1 then 'ALL'
	else CarCompany end as CarCompany,
count(*) as CountTotal
from dbo.Car_Info
group by  GROUPING SETS(EngineType, CarCompany, ())
order by  G_ID

Output:

G_ID

EngineType

CarCompany

CountTotal

1

diesel

ALL

4

1

petrol

ALL

5

2

ALL

Honda

1

2

ALL

Mahindra

1

2

ALL

Maruti

4

2

ALL

TATA

3

3

ALL

ALL

9

Here G_ID represents the level of grouping.

 

  • Interesting Facts about GROUP BY Clause

Feasible Queries:
Example 1

select
EngineType, CarCompany, count(*) as CountTotal
from dbo.Car_Info
group by CarCompany, EngineType

Output:

EngineType

CarCompany

CountTotal

diesel

Mahindra

1

diesel

Maruti

1

diesel

TATA

2

petrol

Honda

1

petrol

Maruti

3

petrol

TATA

1

Example 2:

select
EngineType + '-' + CarCompany as [Car-Fuel Type], count(*) as CountTotal
from dbo.Car_Info
group by CarCompany, EngineType

Output

Car-Fuel Type

CountTotal

diesel-Mahindra

1

diesel-Maruti

1

diesel-TATA

2

petrol-Honda

1

petrol-Maruti

3

petrol-TATA

1

Example 3:

select
EngineType + CarCompany as [Car-Fuel Type], count(*) as CountTotal
from dbo.Car_Info
group by EngineType + CarCompany

Output:

Car-Fuel Type

CountTotal

dieselMahindra

1

dieselMaruti

1

dieselTATA

2

petrolHonda

1

petrolMaruti

3

petrolTATA

1

Example 4:

select
EngineType + CarCompany + ' [Mixed]' as [Car-Fuel Type], count(*) as CountTotal
from dbo.Car_Info
group by EngineType + CarCompany

Output:

Car-Fuel Type

CountTotal

dieselMahindra [Mixed]

1

dieselMaruti [Mixed]

1

dieselTATA [Mixed]

2

petrolHonda [Mixed]

1

petrolMaruti [Mixed]

3

petrolTATA [Mixed]

1

Unfeasible Queries:  Show error on execution

Example 1:

select
EngineType + '-' + CarCompany as [Car-Fuel Type], count(*) as CountTotal
from dbo.Car_Info
group by CarCompany + EngineType

Example 2:

select
EngineType + '-' + CarCompany as [Car-Fuel Type], count(*) as CountTotal
from dbo.Car_Info
group by EngineType + CarCompany

Example 3:

select
EngineType , CarCompany, count(*) as CountTotal
from dbo.Car_Info
group by EngineType + CarCompany

References:
http://technet.microsoft.com/en-us/library/ms177673.aspx
http://technet.microsoft.com/en-us/library/ms173245.aspx
Microsoft Books Online

Categories: T - SQL
  1. April 23rd, 2013 at 05:36 | #1

    It is appropriate time to make a few plans for the longer term and it is
    time to be happy. I have read this publish and if I could I wish
    to counsel you few attention-grabbing things or tips.
    Maybe you can write next articles relating to this article.

    I wish to learn even more things approximately it!

  2. Rohin Kaushik
    June 13th, 2013 at 16:17 | #2

    Very Intersting and knowledgeable

  1. No trackbacks yet.
You must be logged in to post a comment.