Author 
Topic 

2revup
Posting Yak Master
112 Posts 
Posted  04/21/2013 : 20:07:03

Hi Guys need some help with converting this part of a query from MySQL, I need to run this on MSSQL.
Select (SUM(if(rating = 5, 1, 0))SUM(if(RATING < 4, 1, 0)))/SUM(if(RATING > 0, 1, 0))*100 as RECENT_NPS, SUM(if(RATING > 0, 1, 0)) as RECENT_RATINGS, ............


MuMu88
Aged Yak Warrior
547 Posts 
Posted  04/21/2013 : 20:41:28

You may want to use case statements where you currently have if statements; for example replace
SUM(if(rating = 5, 1, 0))
with
SUM(CASE rating
WHEN 5 THEN 1
WHEN 1 THEN 1
WHEN 0 THEN 1
ELSE 0
END)



2revup
Posting Yak Master
112 Posts 
Posted  04/21/2013 : 20:54:03

Thanks for that would the < and > be something like: SUM(CASE rating < 4 THEN 1 < 1 THEN 1 < 0 THEN 1 ELSE 0 END)
? 


2revup
Posting Yak Master
112 Posts 
Posted  04/21/2013 : 21:04:53

I think it should be this could you please confirm?
Select SUM(case rating when 5 then 1 when 1 then 1 when 0 then 1 else 0 end) SUM(case when RATING < 4 then 1 when rating < 1 then 1 when rating < 0 then 1 else 0 end)/ SUM(case when RATING > 0 then 1 when rating > 1 then 1 when rating > 0 then 1 else 0 end)*100 as RECENT_NPS, 


MuMu88
Aged Yak Warrior
547 Posts 
Posted  04/21/2013 : 22:46:50

That should do it. Just make sure to enclose your numerator and denominator in parenthesis. 


bandi
Flowing Fount of Yak Knowledge
India
2224 Posts 
Posted  04/22/2013 : 01:21:01

quote: Originally posted by MuMu88
You may want to use case statements where you currently have if statements; for example replace
SUM(if(rating = 5, 1, 0))
with
This should be
SUM(CASE rating
WHEN 5 THEN 1
ELSE 0
END
)
 Chandu 


bandi
Flowing Fount of Yak Knowledge
India
2224 Posts 
Posted  04/22/2013 : 01:26:23

quote: Originally posted by 2revup
Hi Guys need some help with converting this part of a query from MySQL, I need to run this on MSSQL. Select (SUM(if(rating = 5, 1, 0))SUM(if(RATING < 4, 1, 0)))/SUM(if(RATING > 0, 1, 0))*100 as RECENT_NPS, SUM(if(RATING > 0, 1, 0)) as RECENT_RATINGS, ............
Select
(SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END)SUM(CASE WHEN RATING < 4 THEN 1 ELSE 0 END))/SUM(CASE WHEN RATING > 0 THEN 1 ELSE 0 END)*100 as RECENT_NPS,
SUM(CASE WHEN RATING > 0 TEHN 1 ELSE 0 END) as RECENT_RATINGS
 Chandu 


MuMu88
Aged Yak Warrior
547 Posts 
Posted  04/22/2013 : 14:01:48

My bad. I misinterpreted MYSQL IF statement... Thanks for the correction Chandu. 



Topic 
