SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting the percentage of records with a certain value

    I have a mysql table that has a column that is either 1 or 0. Each row also has a user_id. I'm trying to figure out what percentage of all the rows of a certain user_id has the value 1.

    What would be an efficient mysql query to do this?
    Ohai!

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
    	COUNT(t.MyField = 1 OR NULL) AS UsersWithOne
    ,	COUNT(user_id) AS TotalUsers
    FROM
    	User u
    Or, in a single query:
    Code:
    SELECT
    	((COUNT(t.MyField = 1 OR NULL) / COUNT(user_id)) * 100) AS Percentage
    FROM
    	User u

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    putting a boolean expression into an aggregate function, hoping that TRUE evaluates to the number 1 and can therefore be counted, is something that only mysql supports -- i would always advise using a proper CASE expression instead

    however, in this case there's an even easier solution
    Code:
    select 100.0 * sum(MyField) / count(*) as Percentage
      from User 
     where user_id = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy,

    With regards to the case expression do you mean:

    Code:
    SELECT
     	COUNT(
     		CASE
     			WHEN t.MyField = 1 THEN 1
     			ELSE NULL
     		END
     	) AS UsersWithOne
     ,	COUNT(*) AS TotalUsers
     FROM
     	User u
    Also, is it "better" to use the above query over the one below or vice-versa?

    Code:
    SELECT
     	COUNT(
     		CASE
     			WHEN t.MyField = 1 THEN 1
     			ELSE 0
     		END
     	) AS UsersWithOne
     ,	COUNT(*) AS TotalUsers
     FROM
     	User u
    Cheers

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    actually, if you're going to count them, any non-null value will do, but you should not use ELSE 0, because 0 is non-null and will be counted

    of course, you can leave off the ELSE, eh, and get a NULL that way

    so COUNT(WHEN MyField = 1 THEN 937 END)

    but since the values we're looking for happen to be 1, i much prefer the idea of just summing them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy!

    I agree the sum is better for this example. I've been using the COUNT method for more complex expressions in a project i'm working on so that's why i used it in my last post


Bookmarks

Posting Permissions

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