Error with COUNT(CASE WHEN...)

I have a strange problem.
I have reduced the problem to this:
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
I exppect this to give me the result a-tally = 1 and b-tally = 0.
However I get
a_tally b_tally
-- --
1 1
(1 row(s) affected)
How in earths name is this possible?
/kCOUNT doesn't care if the result is 1 or 0, only that there is a result. I
think you meant to use SUM as the aggregate, not COUNT.
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||Run this:
SELECT count(CASE WHEN x0 = 'A' THEN 10 ELSE 0 END) AS a_tally
,count(CASE WHEN x0 = 'B' THEN 10 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
It will still return the same results as COUNT returns the number of rows.
Change your COUNTs to SUMs and your query works just fine.
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||Just remove the Else part and you'll be fine.
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
http://toponewithties.blogspot.com/...count-them.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||I was trying this: http://www.dbazine.com/ofinterest/oi-articles/celko14
Seems like SQLserver and mr Celko disagrees about COUNT.
Thanks everyone.
/k
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
> I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||I am trying do do like Celko does in this article
http://www.dbazine.com/ofinterest/oi-articles/celko14
Do you any advice regarding the usage of the count in the HAVING clause?
My solution was this
HAVING COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE null END)
<> COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE null END)
However that means I have to set ansiwarnings off, which I dont like.
/k
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOdJqmi2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> COUNT doesn't care if the result is 1 or 0, only that there is a result.
I
> think you meant to use SUM as the aggregate, not COUNT.
>
>
> "kurt sune" <apa@.apa.com> wrote in message
> news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||> Do you any advice regarding the usage of the count in the HAVING clause?
> My solution was this
> HAVING COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE null END)
> <> COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE null END)
Yes, use SUM, just like what was suggested in the SELECT clause?