Percentage of records meeting criteria

  • I'm wondering if there is a way to calculate the percentage of records meeting a certain criteria within a single statement. I have a table with a datetime field and would like to know what percentage have that datetime value earlier than a specific date.

    Currently I'm selecting the total record count into a variable, then the count that meets my criteria, and dividing those two results to get my percentage. It works just fine but I'm wondering if there is a more elegant way to go about this.

    Thanks,

    Paul

  • SELECT COUNT(*) -- all rows

    *100/

    COUNT(CASE WHEN DateCol > @DateEdge THEN 1 ELSE NULL END) -- only rows meeting criteria to be counted

    FROM ...

    _____________
    Code for TallyGenerator

  • Shouldn't the count of CASE be the numerator?

    As in -

    SELECT

    COUNT(CASE WHEN DateCol > @DateEdge THEN 1 ELSE NULL END) -- only rows meeting criteria to be counted

    *100/

    COUNT(*) -- all rows

    FROM ...

    Also - if you need more precision - cast the count to a float.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Yes, of course.

    Let's say, it was an intentional bug to make OP to analize and understand the code, not to just copy-paste it.

    😉

    Does it work as an excuse? 😛

    And just in case the table is empty I'd put

    NULLIF(COUNT(*), 0)

    to avoid division by zero.

    _____________
    Code for TallyGenerator

  • Sergiy (6/12/2008)


    Yes, of course.

    Let's say, it was an intentional bug to make OP to analize and understand the code, not to just copy-paste it.

    😉

    Does it work as an excuse? 😛

    And just in case the table is empty I'd put

    NULLIF(COUNT(*), 0)

    to avoid division by zero.

    Better excuse than I've been able to come up with in a while 🙂

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks for the help. Now I know how to work multiple Count() expressions into a single select. Didn't think about using Case in that way before. I think this will help me on a few other things as well.

    My OP said I count the total first, and then the filtered count. So you can blame my poorly worded question for the numerator/denominator mixup 😉

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

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