SQL Lion

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:  

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:

 And the data associated to it are:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

• 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:

• 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: 

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: 

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: 

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:

• 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:

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:

Example 2:

SELECT
EngineType + '-' + CarCompany AS [Car-Fuel Type], COUNT(*) AS CountTotal
FROM dbo.Car_Info
GROUP BY CarCompany, EngineType

Output: 

Example 3:

SELECT
EngineType + CarCompany AS [Car-Fuel Type], COUNT(*) AS CountTotal
FROM dbo.Car_Info
GROUP BY EngineType + CarCompany

Output:

Example 4:

SELECT
EngineType + CarCompany + ' [Mixed]' AS [Car-Fuel Type], COUNT(*) AS CountTotal
FROM dbo.Car_Info
GROUP BY EngineType + CarCompany

Output:

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