Reputation: 4292
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
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
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
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
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