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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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