**News Alert:**Experts Exchange Confirmed as Safe in Cloudbleed Leak Read More

Solved

Posted on 2007-03-27

I am trying to protect a divide by zero in my last column.

SELECT

max(dbo.getmonthdate(t.TransDate)),

max(dbo.getmonth(t.TransDate)),

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,(Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS Calculated

The problem is that

(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)

may sometimes be zero in which case I need to return "No Ratio Available"

Can Anyone help? I understand I may have to use a nested case statement or something!

SELECT

max(dbo.getmonthdate(t.Tra

max(dbo.getmonth(t.TransDa

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,(Sum(CA

The problem is that

(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)

may sometimes be zero in which case I need to return "No Ratio Available"

Can Anyone help? I understand I may have to use a nested case statement or something!

6 Comments

(

SELECT

max(dbo.getmonthdate(t.Tra

max(dbo.getmonth(t.TransDa

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,(Sum(CA

From MyTable) Calculation

For example, doing a secondary select means that you can work with the totals without having to recalculate them.

Select MaxMonthDate,MaxMonth,Pass

, Case When PassportsIssued = 0 Then 'No Ratio Available'

Else Convert(varchar(30),passpo

End As Calculated

From

(

SELECT

max(dbo.getmonthdate(t.Tra

max(dbo.getmonth(t.TransDa

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications

From MyTable) as X

max(dbo.getmonthdate(t.Tra

max(dbo.getmonth(t.TransDa

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,

CASE WHEN t.TransType =1 THEN CAST((Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) as varchar(2000) ) ELSE 'No Ratio Available' END AS Calculated

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Title | # Comments | Views | Activity |
---|---|---|---|

Get the latest status | 8 | 32 | |

I'm looking to copy specific data from one SQL table to another, and need help with the query. | 5 | 20 | |

Return 0 on SQL count | 24 | 30 | |

Show Results for Latest DateTime in a View | 27 | 25 |

Join the community of 500,000 technology professionals and ask your questions.