SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query: Add a column using case and group by it
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HarryCallaghan
Starting Member

5 Posts

Posted - 02/25/2013 :  07:49:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/25/2013 :  07:53:26  Show Profile  Reply with Quote
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

Go to Top of Page

HarryCallaghan
Starting Member

5 Posts

Posted - 02/25/2013 :  09:11:31  Show Profile  Reply with Quote
Thanks a lot! It works
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/25/2013 :  10:09:28  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000