Results 1 to 10 of 10
  1. #1
    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?

    Thanks in advance!

    Kevin

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    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. #5
    Join Date
    Feb 2004
    Posts
    18
    Quote 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. #6
    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. #7
    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 ..
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •