var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Percent of columns with a certain value
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!
Sure, this is for SQL Server, but it should work on any SQL-92 compliant engine. I'd use:
SELECT Sum(CASE WHEN 'sa' = loginame THEN 1e2 END) / Count(*)
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:
I'm looking for a result of 66.66 (% of rows where y=9). I should have also mentioned that I'm using Ingres.
Last edited by weaselboy1976; 05-20-04 at
I'm using Ingres, and there is no "set @" abilities. I need a pure SQL-92 solution.
Originally Posted by
Ok, this gets really close:
select ((count (case when y!=9 then null else y end))*100) / (count(*))
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 ..
Get yourself a copy of the
The Holy Book
order has no physical Brett in The meaning of a Kaiser . -database data
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!!!!