Home > Common Table Expressions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > TSQL – Common Table Expressions (CTE) – Part 3 – GROUP BY calculated column

TSQL – Common Table Expressions (CTE) – Part 3 – GROUP BY calculated column


There are other ways too which can be used to GROUP BY a calculated column such as using a derived table.

Consider, the below data which needs to be categorized based on the Cost Range.

 

USE AdventureWorks2008R2
SELECT StandardCost, ProductID
FROM Production.Product

 

image

 

The data can be categorized using a simple query, which divides the Standard Cost columns into required ranges:

SELECT CASE
WHEN StandardCost BETWEEN    0 AND  499 THEN 'Low'
WHEN StandardCost BETWEEN  500 AND  999 THEN 'Medium'
WHEN StandardCost BETWEEN 1000 AND 1499 THEN 'High'
WHEN StandardCost >=      1500          THEN 'Very High'
END AS [Cost Range], COUNT(ProductID)
FROM Production.Product
GROUP BY 1

 

but, if you try to execute the above query you will receive an error:

Msg 164, Level 15, State 1, Line 8 Each GROUP BY expression must contain at least one column that is not an outer reference.

What is this error? well, in simple terms we are trying to group by before we retrieve all data. How to avoid this error? There are two ways.

1. Use a CTE:

;WITH scTable([Cost Range], ProductID)
AS
(
    SELECT CASE
    WHEN StandardCost BETWEEN    0 AND  499 THEN 'Low'
    WHEN StandardCost BETWEEN  500 AND  999 THEN 'Medium'
    WHEN StandardCost BETWEEN 1000 AND 1499 THEN 'High'
    WHEN StandardCost >=      1500          THEN 'Very High'
    END AS [Cost Range], ProductID
    FROM Production.Product
)

 

we are not using GROUP BY inside the CTE here, this will simply retrieve the data, and after that we can us use GROUP BY:

 

SELECT [Cost Range], COUNT(ProductID) AS ProductsInRange
FROM scTable
GROUP BY [Cost Range]

 

and, here’s the required output:

 

image

2. Using derived table: Same output can be achieved using a derived table:

SELECT dTable.[Cost Range], COUNT(dTable.[ProductID]) AS ProductsInRange
FROM
(
    SELECT CASE
    WHEN StandardCost BETWEEN    0 AND  499 THEN 'Low'
    WHEN StandardCost BETWEEN  500 AND  999 THEN 'Medium'
    WHEN StandardCost BETWEEN 1000 AND 1499 THEN 'High'
    WHEN StandardCost >=      1500          THEN 'Very High'
    END AS [Cost Range], ProductID
    FROM Production.Product
) AS dTable
  GROUP BY dTable.[Cost Range]

 

image

 

And, the execution plans generated will also be identical. so either can be used in such a scenario. image

You can download the sample scripts used in this example here.

Hope This Helps!

Vishal

  1. Werner Husi
    12.06.2013 02:13 | #1

    That is all good and fine. Now that we have the result either way we want to join the result to another table. I don’t seem to be able to do this with the grouped result of a CTE?

  2. Nick Xu
    26.03.2014 05:22 | #2

    Very nicely done, thanks again for posting this fine explanation of CTE.

  1. No trackbacks yet.