Solved

Calulated Column Returns error apon dividing by Zero

Posted on 2007-03-28
9
181 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 250 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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

803 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