Hello

I want to calculate the percentage of table rows with a certain value.

So ((# rows with column = value) / (total # of rows in table)) * 100

Does anyone know how to do this in one SQL statement?

Thanks in advance!

Kevin

Sure, this is for SQL Server, but it should work on any SQL-92 compliant engine. I'd use:-PatP

set @YourPercentage = 100 * ((select count(*) from YourTable where YourColumn = @YourValue)/(select count(*) from YourTable))

There are other methods which may be more efficient if you are trying to calculate for more than one Value at a time.

>> SELECT Sum(CASE WHEN 'sa' = loginame THEN 1e2 END) / Count(*)
>> FROM master.dbo.sysprocesses

I don't understand what the case expression is trying to accomplish?

If the table in question was "mytable" and had columns "X" & "Y" and values like:

X Y
1 9
2 9
3 7
4 7
5 9
6 9

I'm looking for a result of 66.66 (% of rows where y=9). I should have also mentioned that I'm using Ingres.

Thanks again

I'm using Ingres, and there is no "set @" abilities. I need a pure SQL-92 solution.

Thanks!

Ok, this gets really close:

select ((count (case when y!=9 then null else y end))*100) / (count(*))
from mytable;

The only thing is, the result is an integer (66) instead of a float (66.66).

what you are trying to do is dividing an int by an int .. so the answer is int

you will need to cast the denominator as float to achieve 66.66 ..

weaselboy, your solution is the same as pat's

pat's solution is quite clever

perhaps not the best (since others, who come after, may need to stop and figure it out again)

but it works, and furthermore it avoids an arithmetic operation, too

While using Pat's "SUM" based solution:

(1) I'm assuming that the "1e2" part of Pat's solution is the same as "1 ELSE 2".
(2) when there are no rows with the matching column, I'm getting a result of "1" instead of the desired "0". Maybe I'm doing something wrong?
(3) So, I don't think r937's "your solution is the same as pat's" comment is correct.

1. no it isn't -- perhaps you should try it and see what 1e2 actually is
2. when there are no rows, you are getting what? with whose query?
3. well yours and pat's are sort of the same in that you aren't counting nulls and he's not summing them!!!!