Using CASE statement in ORDER BY clause Sql 2005

Hi, I have a set of rows that I need to sort them in order by using case statement.
How can this be done?

sample rows

entity, sales, psales,tsales ( columns )
p1,50,0,100
p2,0,30,90
p3,30,60,0
p4,3,1,2

sorting criteria ( way I need to ouput results)
when entity sales > 0 and tsales > 0, then tsales + psales desc
when entity sales = 0, then tsales + psales desc
when entity sales > 0 and tsales = 0, then sales+psales desc

would order by group similar rows ( based on above ) and sort them in 3 different ways ?

Thanks,
ivan_belalAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BrandonGalderisiConnect With a Mentor Commented:
Try:

select case
  when sales > 0 and tsales = 0 then sales + psales
  when sales > 0 then tsales+psales
  when sales = 0 then stales + psales
end as SortColumn, entity,sales,psales,tsales
from YOURTABLE
order by 1 desc
0
 
ivan_belalAuthor Commented:
I been to that link. It shows dynamic order by. My case is not. Can you / someone test out my example and provide me with a few snippets would be helpful, thahks.
0
 
vdr1620Commented:
Try that
CREATE TABLE #T
(
Entity Varchar(100),
Sales int,
psales int,
tsales int
)

INSERT INTO #T VALUES ('p1',50,0,100)
INSERT INTO #T VALUES ('p2',0,30,90)
INSERT INTO #T VALUES ('p3',30,60,0)
INSERT INTO #T VALUES ('p4',3,1,2)

SELECT * FROM #T
ORDER BY CASE WHEN  sales > 0 and tsales > 0 then (tsales + psales ) 
when sales = 0 then tsales + psales 
when sales > 0 and tsales = 0 then sales+psales END Desc

Open in new window

0
 
ivan_belalAuthor Commented:
Looks like BrandonGalderisi's solution I needed. I will get back after a few tests, thanks...
0
All Courses

From novice to tech pro — start learning today.