How to fill in nulls in a set with zeroes?

Question

Post reply

How to fill in nulls in a set with zeroes?

keymoo

SSCrazy

Points: 2975
More actions
January 17, 2011 at 7:13 am

#232312

I have the following table:
drop table #trades
go
create table #trades (
tradeId int not null primary key nonclustered,
pctAccountChange decimal (38,6) not null,
symbol char(6) not null
)
go
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(78,1.152172,'GBPCHF')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(116,0.688740,'AUDJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(99,0.509932,'CADJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(63,-0.283782,'EURJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(174,-0.179938,'EURCHF')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(17,-1.910000,'GBPUSD')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(139,-0.391604,'GBPJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(150,-0.404151,'USDCHF')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(64,-0.356655,'EURJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(126,0.745437,'EURGBP')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(18,0.275862,'GBPUSD')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(79,1.159340,'GBPCHF')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(65,0.893750,'EURJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(1,1.375000,'EURUSD')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(161,0.885134,'CHFJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(152,0.454544,'USDCHF')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(140,0.282684,'GBPJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(39,-0.987500,'USDJPY')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(127,0.321225,'EURGBP')
INSERT INTO [#trades] ([tradeId],[pctAccountChange],[symbol])VALUES(80,0.048542,'GBPCHF')
go
I want to show the numbers of winners losers and breakevens for each symbol. If there are none for a category then I want to show a zero. This query works, but does not include zeroes. How do I include the zeroes?
select
COUNT(*) as numTrades,
case
when b.pctAccountChange > 0.1 then 'Win'
when b.pctAccountChange < -0.1 then 'Loss'
else 'BreakEven'
end as Type,
b.symbol
from #trades b
group by
case
when b.pctAccountChange > 0.1 then 'Win'
when b.pctAccountChange < -0.1 then 'Loss'
else 'BreakEven'
end,
symbol

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

Hi,

Change your select statement to have the following:

;

WITH cte

AS ( SELECT

COUNT(*) AS numTrades ,

CASE WHEN b.pctAccountChange > 0.1 THEN 'Win'

WHEN b.pctAccountChange < -0.1 THEN 'Loss'

ELSE 'BreakEven'

END AS Type ,

b.symbol

FROM

#trades b

GROUP BY

CASE WHEN b.pctAccountChange > 0.1 THEN 'Win'

WHEN b.pctAccountChange < -0.1 THEN 'Loss'

ELSE 'BreakEven'

END ,

symbol

)

SELECT

*

INTO

#results

FROM

[cte] PIVOT

( SUM([numTrades]) FOR [Type] IN ( [Win], [Loss], [BreakEven] ) )

AS p

ORDER BY

[symbol]

SELECT

[Symbol] ,

ISNULL([Win], 0) AS 'Win' ,

ISNULL([Loss], 0) AS 'Loss' ,

ISNULL([BreakEven], 0) AS 'BreakEven'

FROM

[#results]

ORDER BY

[Symbol]

I also added this to the top of your original script just for completeness:

DROP TABLE #results

It's not the prettiest solution but it should do what you want it to.

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

CELKO (1/17/2011)
CASE
WHEN B.account_change_pcT > 0.1 THEN 'Win'
WHEN B.account_change_pct < -0.1 THEN 'Loss'
WHEN B.account_change_pct <= 0.1 THEN 'BreakEven'
WHEN B.account_change_pct >= -0.1 THEN 'BreakEven'
ELSE NULL END AS trade_type
This is sneaky. The WHEN clauses are tested in order. by the time the testinfg gets to the third and fourth WHEN, we have winners and losers. The two tests avoids an OR or BETWEEN

Good sneak, Joe but the original code had neither OR or BETWEEN in it and the code above still doesn't solve the problem of having and entry for each category even if that category is missing for a symbol.

Also, I can understand why you might usually want to avoid OR but why would you want to avoid BETWEEN if the end-points are inclusive as they are for this drill?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

s_osborne2 (1/17/2011)
Hi,
Change your select statement to have the following:
;
WITH cte
AS ( SELECT
COUNT(*) AS numTrades ,
CASE WHEN b.pctAccountChange > 0.1 THEN 'Win'
WHEN b.pctAccountChange < -0.1 THEN 'Loss'
ELSE 'BreakEven'
END AS Type ,
b.symbol
FROM
#trades b
GROUP BY
CASE WHEN b.pctAccountChange > 0.1 THEN 'Win'
WHEN b.pctAccountChange < -0.1 THEN 'Loss'
ELSE 'BreakEven'
END ,
symbol
)
SELECT
*
INTO
#results
FROM
[cte] PIVOT
( SUM([numTrades]) FOR [Type] IN ( [Win], [Loss], [BreakEven] ) )
AS p
ORDER BY
[symbol]
SELECT
[Symbol] ,
ISNULL([Win], 0) AS 'Win' ,
ISNULL([Loss], 0) AS 'Loss' ,
ISNULL([BreakEven], 0) AS 'BreakEven'
FROM
[#results]
ORDER BY
[Symbol]
I also added this to the top of your original script just for completeness:
DROP TABLE #results
It's not the prettiest solution but it should do what you want it to.

Just to follow up on that... a good ol' fashioned cross-tab can make life real easy and "prettier".

SELECT Symbol,

SUM(CASE WHEN b.pctAccountChange > 0.1 THEN 1 ELSE 0 END) AS Win,

SUM(CASE WHEN b.pctAccountChange < -0.1 THEN 1 ELSE 0 END) AS Loss,

SUM(CASE WHEN ABS(b.pctAccountChange) <= 0.1 THEN 1 ELSE 0 END) AS BreakEven,

COUNT(*) AS NumTrades

FROM #trades b

GROUP BY Symbol

ORDER BY Symbol

;

Cross-Tabs are faster than Pivots, as well. Take a look at the following article for more on the comparison between Cross-Tabs and Pivots and how to use them to change rows to columns... speed test results are included near the end of the article.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

keymoo SSCrazy Points: 2975 More actions · Answer 4

Jeff Moden
Just to follow up on that... a good ol' fashioned cross-tab can make life real easy and "prettier".
SELECT Symbol,
SUM(CASE WHEN b.pctAccountChange > 0.1 THEN 1 ELSE 0 END) AS Win,
SUM(CASE WHEN b.pctAccountChange < -0.1 THEN 1 ELSE 0 END) AS Loss,
SUM(CASE WHEN ABS(b.pctAccountChange) <= 0.1 THEN 1 ELSE 0 END) AS BreakEven,
COUNT(*) AS NumTrades
FROM #trades b
GROUP BY Symbol
ORDER BY Symbol
;
Cross-Tabs are faster than Pivots, as well.

Beautiful Jeff, thanks. KISS, eh? 🙂 I like the ABS flourish as well.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

keymoo (1/18/2011)
Jeff Moden
Just to follow up on that... a good ol' fashioned cross-tab can make life real easy and "prettier".
SELECT Symbol,
SUM(CASE WHEN b.pctAccountChange > 0.1 THEN 1 ELSE 0 END) AS Win,
SUM(CASE WHEN b.pctAccountChange < -0.1 THEN 1 ELSE 0 END) AS Loss,
SUM(CASE WHEN ABS(b.pctAccountChange) <= 0.1 THEN 1 ELSE 0 END) AS BreakEven,
COUNT(*) AS NumTrades
FROM #trades b
GROUP BY Symbol
ORDER BY Symbol
;
Cross-Tabs are faster than Pivots, as well.
Beautiful Jeff, thanks. KISS, eh? 🙂 I like the ABS flourish as well.

Heh... yeah. KISS is one of my favorites. The ABS flourish is because I'm lazy... saved a bit of typing and still runs fast. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)