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 2012 Forums
 Analysis Server and Reporting Services (2012)
 Help please

Author  Topic 

saragodia
Starting Member

2 Posts

Posted - 2013-08-23 : 11:20:43
hi, sorry if it's too ametuere question but I am stuck with this for couple of days now. :(

I am trying to replicate mySQL query into SQL server. Within my SQL we used to use a field BBA which carried always 1 for each booking.

so the query in mySQL was
(SUM(IF(AdonProfit > 0,BBA,0))) AS No_ofAdons

however in SQL server we do not have a field BBA and therefore do not use SUM. Instead I use count distinct ConfNo (confirmations numbers) in order to count total adons sold.

So in SQL server I have written the query as

count(distinct (if(a.AdonProfit > 0,a.ConfNo,0))) as No_ofAdons

But it's not working for me. Any help would be much appropriated.

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-23 : 11:42:45
Are you trying to get the number of entries for which AdonProfit is greater than zero, or sum up the add on profits? Assuming the former:
SUM(IIF(AdonProfit > 0, 1, 0))
Go to Top of Page

saragodia
Starting Member

2 Posts

Posted - 2013-08-23 : 11:52:05
quote:
Originally posted by James K

Are you trying to get the number of entries for which AdonProfit is greater than zero, or sum up the add on profits? Assuming the former:
SUM(IIF(AdonProfit > 0, 1, 0))




I am trying to get the number of entries for which adProfit is greater than zero. Thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-23 : 14:56:21
quote:
Originally posted by saragodia

quote:
Originally posted by James K

Are you trying to get the number of entries for which AdonProfit is greater than zero, or sum up the add on profits? Assuming the former:
SUM(IIF(AdonProfit > 0, 1, 0))




I am trying to get the number of entries for which adProfit is greater than zero. Thanks

Can you try the query I posted? That is exactly what it is meant to do.
Go to Top of Page
   

- Advertisement -