T-SQL and Group By

[Goksly]

Bit confused by this and I cannot get any consistancy. Say I have a select statement that has a big case statement which I call 'x'. Sometimes I am able to say 'Group By x' and other times it says this is an unknown column and I have to copy and paste the case statement after the group by which works (but is a pain - duplication).

Does anyone know why?

 

[Pho]

You can use a subquery.

SELECT   COUNT(ProductDescriptionID) AS COUNT,
         range
FROM
         (
           SELECT [ProductDescriptionID],
                 CASE
                         WHEN ProductDescriptionID BETWEEN 0 AND 1000
                         THEN '0-1000'
                         WHEN ProductDescriptionID BETWEEN 1000 AND 2000
                         THEN '1000-2000'
                         WHEN ProductDescriptionID BETWEEN 2000 AND 3000
                         THEN '2000-3000'
                 END
                 AS range
           FROM    [AdventureWorks].[Production].[ProductDescription]
         ) sub
GROUP BY range
ORDER BY range

(Uses AdventureWorks)

Apparently you can't use an alias (range in my example) in a WHERE or GROUP BY clause etc.

 

[Goksly]

Apparently you can't use an alias (range in my example) in a WHERE or GROUP BY clause etc.

But I have in the past... which was the source of my confusion (no consistancy). By past I mean a week or so ago... on the same day a similar query wouldnt let me. Megh!

Thanks for your post.