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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. Werner Husi
    June 12, 2013 at 2:13 pm

    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. March 26, 2014 at 5:22 am

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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s