CODEWITHSUNDEEP
sqlsql-server
Behrooz Karjoo
Behrooz Karjoo

Reputation: 4292

Divide by 0 error in Query

If I run this Query: 

SELECT 
   [BBox]
  ,SUM(GrossPnL) AS 'Gross'
  ,SUM(SharesTraded) AS 'Shares'
  ,(SUM(GrossPnL)/SUM(SharesTraded)) AS 'CPS'
FROM [PrimusGroup].[dbo].[PrmsBlotter]
WHERE [RunType] = 'Backtesting'
GROUP BY [BBox]

I get divide by 0 error for cases where there's 0 shares traded.

I tried to fix this by: 

SELECT 
   [BBox]
  ,SUM(GrossPnL) AS 'Gross'
  ,SUM(SharesTraded) AS 'Shares'
  ,(IF SUM(SharesTraded) > 0 
    (SUM(GrossPnL)/SUM(SharesTraded)) 
   ELSE 
     SUM(GrossPnL)) AS 'CPS'
FROM [PrimusGroup].[dbo].[PrmsBlotter]
WHERE [RunType] = 'Backtesting'
GROUP BY [BBox]

However I get Incorrect syntax near sum. Am I doing something wrong?

Upvotes: 0

Views: 110

Answers (4)

Franz Stoneking
Franz Stoneking

Reputation: 35

I don't see what the desired output is if the 'SharesTraded' = 0 but treating 0 and 1 as the same value (what you are doing above) seems wrong.

Assuming 0s are special cases, you case use the CASE WHEN situation and just make the 'ELSE' NULL but I like to use the 'NULLIF' function when possible.

So:

(SUM(GrossPnL)/SUM(SharesTraded)) AS 'CPS'

Becomes:

(SUM(GrossPnL)/NULLIF(SUM(SharesTraded),0)) AS 'CPS'

This will result in 'CPS' of NULL when 'SharesTraded' = 0 by setting SUM(SharesTraded) to 0, thus resolving the error.

Not sure what this looks like in the show-plan but it helps keep code clean and concise.

Upvotes: 0

Igor
Igor

Reputation: 62318

You can use IIF

SELECT [BBox]
  ,SUM(GrossPnL) AS 'Gross'
  ,SUM(SharesTraded) AS 'Shares'
  ,IIF(SUM(SharesTraded) > 0, SUM(GrossPnL)/SUM(SharesTraded), SUM(GrossPnL)) AS 'CPS'
FROM [PrimusGroup].[dbo].[PrmsBlotter]
WHERE [RunType] = 'Backtesting'
GROUP BY [BBox]

Upvotes: 0

starko
starko

Reputation: 1149

Use IIF:

 SELECT 
       [BBox]
      ,SUM(GrossPnL) AS 'Gross'
      ,SUM(SharesTraded) AS 'Shares'
      ,(IIF SUM(SharesTraded) > 0, 
        (SUM(GrossPnL)/SUM(SharesTraded)), 
         SUM(GrossPnL)) AS 'CPS'
    FROM [PrimusGroup].[dbo].[PrmsBlotter]
    WHERE [RunType] = 'Backtesting'
    GROUP BY [BBox]

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use a case expression.

SELECT 
   [BBox]
  ,SUM(GrossPnL) AS 'Gross'
  ,SUM(SharesTraded) AS 'Shares'
  ,CASE WHEN SUM(SharesTraded) > 0 THEN SUM(GrossPnL)/SUM(SharesTraded)
   ELSE SUM(GrossPnL) END AS 'CPS'
FROM [PrimusGroup].[dbo].[PrmsBlotter]
WHERE [RunType] = 'Backtesting'
GROUP BY [BBox]

Upvotes: 4

Related Questions