# Thread: Percent of columns with a certain value

1. Registered User
Join Date
Feb 2004
Posts
18

## Unanswered: Percent of columns with a certain value

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?

Kevin

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Sure, this is for SQL Server, but it should work on any SQL-92 compliant engine. I'd use:
Code:
```SELECT Sum(CASE WHEN 'sa' = loginame THEN 1e2 END) / Count(*)
FROM master.dbo.sysprocesses```
-PatP

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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.

4. Registered User
Join Date
Feb 2004
Posts
18
>> 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
Last edited by weaselboy1976; 05-20-04 at 09:42.

5. Registered User
Join Date
Feb 2004
Posts
18
Originally Posted by blindman
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.
I'm using Ingres, and there is no "set @" abilities. I need a pure SQL-92 solution.

Thanks!

6. Registered User
Join Date
Feb 2004
Posts
18
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).

7. The SQL Apostle
Join Date
Jul 2003
Location
The Dark Planet
Posts
1,401
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 ..

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

9. Registered User
Join Date
Feb 2004
Posts
18
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.

10. SQL Consultant
Join Date
Apr 2002
Location