By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

Any way to easily add this computed column (divide by zero problem)

P: n/a

I have this query that I would rather not turn into a stored proc.
because the client really has no budget. I was hoping there would be a
way to add this statement inline to the following sql with some kind of
IF statement without writing it all out. The problem is either total
cost or conversions will have some zeros in the table.

SUM([total cost]/[cost per conversion])

Any help would be appreciated - Happy New Year

SELECT [Search Term], SUM([total cost]/[cost per conversion]) as calcw,
SUM([impressions]) AS impress, SUM([Total Cost]) AS totalcost,
SUM([Total Clicks]) AS totalclicks, SUM(Conversions) AS totalconv,
SUM([Cost Per Conversion]) AS costconv FROM csv where [start date]
='01/01/04' and [end date] <='12/31/04' GROUP BY [Search Term] ORDER

BY [Search Term] ASC

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Do you just want to ignore the row in the sum if either value is zero? If
so:

SUM([total cost]/NULLIF([cost per conversion],0))

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2

P: n/a
I would rather the row be included as just returned as zero

Thanks!

Jul 23 '05 #3

P: n/a
Also I tried that statement and it works well except I think the
calculation comes out incorrect - It needs to be something like the
SUM of total cost divived by the SUM of Conversions.

Thanks

Jul 23 '05 #4

P: n/a
COALESCE( SUM([total cost]) / NULLIF(SUM([cost per conversion]),0) ,0)

--
David Portas
SQL Server MVP
--
Jul 23 '05 #5

P: n/a
Thank you very much David - That seems to have done it!
Thanks again....

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.