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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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