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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 mySQL to MSSQL conversion help

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]
Go to Top of Page

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)

?
Go to Top of Page

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,
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -