Percentage of records meeting criteria

Question

Post reply

Percentage of records meeting criteria

Paul Stafford

SSChasing Mays

Points: 600
More actions
June 12, 2008 at 11:46 am

#70853

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

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

Sergiy SSC Guru Points: 110208 More actions · Answer 3

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

Paul Stafford SSChasing Mays Points: 600 More actions · Answer 5

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 😉