Post reply

Count on Condition Inside Window Function?

  • May 12, 2022 at 6:47 am

    #4031600

    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

    #4031637

    Try changing ELSE 0 to ELSE NULL.

    Otherwise you are just counting zeros rather than ones, which explains the behaviour you are seeing.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • May 12, 2022 at 3:34 pm

    #4031878

    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

    #4031922

    .

  • May 12, 2022 at 10:23 pm

    #4032077

    Hello @Phill Parkin Thank you.

  • May 12, 2022 at 10:24 pm

    #4032078

    @scottpletcher Thank you very much. This was boolean, I should have thought over it. Thanks a lot

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

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