Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calulated Column Returns error apon dividing by Zero

Posted on 2007-03-28
9
Medium Priority
?
196 Views
Last Modified: 2010-03-19
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
Comment
Question by:MayoorPatel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 18806701
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 18806704
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
 
LVL 1

Author Comment

by:MayoorPatel
ID: 18806707
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 28

Expert Comment

by:imran_fast
ID: 18806720
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
 
LVL 1

Author Comment

by:MayoorPatel
ID: 18806724
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 18806731
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
 
LVL 1

Author Comment

by:MayoorPatel
ID: 18806740
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
 
LVL 28

Accepted Solution

by:
imran_fast earned 1000 total points
ID: 18806746
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
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 1000 total points
ID: 18806758
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

609 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question