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.
Author |
Topic |
2revup
Posting Yak Master
112 Posts |
Posted - 2013-04-21 : 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
549 Posts |
Posted - 2013-04-21 : 20:41:28
|
You may want to use case statements where you currently have if statements; for example replace [CODE]SUM(if(rating = 5, 1, 0))[/CODE]with[CODE]SUM(CASE rating WHEN 5 THEN 1 WHEN 1 THEN 1 WHEN 0 THEN 1 ELSE 0 END)[/CODE] |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-04-21 : 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 - 2013-04-21 : 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
549 Posts |
Posted - 2013-04-21 : 22:46:50
|
That should do it.Just make sure to enclose your numerator and denominator in parenthesis. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-22 : 01:21:01
|
quote: Originally posted by MuMu88 You may want to use case statements where you currently have if statements; for example replace [CODE]SUM(if(rating = 5, 1, 0))[/CODE]with[CODE] This should be SUM(CASE rating WHEN 5 THEN 1 ELSE 0 END )[/CODE]
--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-22 : 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
549 Posts |
Posted - 2013-04-22 : 14:01:48
|
My bad.I misinterpreted MYSQL IF statement...Thanks for the correction Chandu. |
|
|
|
|
|
|
|