Query: Add a column using case and group by it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query: Add a column using case and group by it

Author

Topic

HarryCallaghan
Starting Member

5 Posts

Posted - 2013-02-25 : 07:49:11

Hi!

I have a sales table with codes such as "NT34" "A56" "FE45" where the initial NT, VA or FE means that the order comes from one plant or another and i need to group by plant. So i'm trying to add a case statement in the select clause using "like" to add a column that show the plant where the order comes. Problem is, of course, that the select statement is being processed after the group by statement so that strategy doesnt work. ¿Any idea how to solve that?

Some sample code:

select case when pedido_cliente like 'NT%' then 'Engines' when pedido_cliente like 'FE%' then 'Pilot' end as Planta
from dbo.pedidos
group by Planta

Thanks a lot!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 07:53:26

Rather than use the alias, repeat the CASE expression in the group by clause


select case when pedido_cliente like 'NT%' then 'Engines' when pedido_cliente like 'FE%' then 'Pilot' end as Planta
from dbo.pedidos
group by case when pedido_cliente like 'NT%' then 'Engines' when pedido_cliente like 'FE%' then 'Pilot' end

HarryCallaghan
Starting Member

5 Posts

Posted - 2013-02-25 : 09:11:31

Thanks a lot! It works

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 10:09:28

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources