• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Calulated Column Returns error apon dividing by Zero

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
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID

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 me edit my query please.

0
MayoorPatel
Asked:
MayoorPatel
  • 3
  • 3
  • 3
2 Solutions
 
imran_fastCommented:
change this column to be

case when  (CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) = 0 then 0
else
(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)
end
 AS Calculated
0
 
HuyBDCommented:
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) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1) ELSE 0) AS Calculated
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID

HuyBD;
0
 
MayoorPatelAuthor Commented:
Huy Bud im getting

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

Which is an error with this line?

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications, (Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1) ELSE 0) AS Calculated
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
imran_fastCommented:
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,
case when  (CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) = 0 then 0
else
(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)
end
 AS Calculated


FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0
 
MayoorPatelAuthor Commented:
Imran I now get this

Msg 8120, Level 16, State 1, Line 1
Column 'tblTransactions.TransType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

0
 
HuyBDCommented:
sorry, I have missing
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) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1) ELSE 0 END) AS Calculated
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0
 
MayoorPatelAuthor Commented:
Huy Bud still getting

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
with this line

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

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,
case when  sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) = 0 then 0
else
(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)
end
 AS Calculated


FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0
 
HuyBDCommented:
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) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1)) ELSE 0 END) AS Calculated
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now