May 12, 2022 at 6:47 am
Hi Experts,
In the code below, I would like to count only when tc.Isenabled = 'Yes' but now I get total count (Total and Enabled) same value.
Please can you help me what mistake I am doing.?
Select
tc.Tno
,tc.IsEnabled
,tc.IsLoc
,COUNT(CASE WHEN tc.IsEnabled = 'YES' THEN 1 ELSE 0 END) OVER( ) AS Enabled /* Issue is this is counting all */
--,COUNT(tc.IsEnabled = 'No') OVER( Partition by tc.Tno)
,COUNT(tc.IsEnabled) over () Total
-- Total and Enabled value is same
FROM
TotCnt tc
Thanks a lot
May 12, 2022 at 7:42 am
Try changing ELSE 0 to ELSE NULL.
Otherwise you are just counting zeros rather than ones, which explains the behaviour you are seeing.
May 12, 2022 at 3:34 pm
I suggest using SUM rather than COUNT. THEN 1 ELSE 0 is a consistent, clean pattern, whereas THEN 1 ELSE NULL is a kludge.
,SUM(CASE WHEN tc.IsEnabled = 'YES' THEN 1 ELSE 0 END) OVER( ) AS Enabled
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 12, 2022 at 4:27 pm
.
May 12, 2022 at 10:23 pm
Hello @Phill Parkin Thank you.
May 12, 2022 at 10:24 pm
@ScottPletcher Thank you very much. This was boolean, I should have thought over it. Thanks a lot
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy