SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get proportional value for a value for a pie chart

    Hi,
    with this query
    PHP Code:
    SELECT A.domain_idU.gender as fieldDATE(access_start_datetime) as dateCOUNT(A.id) as value
    FROM  stats_access A
    JOIN users U ON U
    .id A.fb_id
    GROUP BY DATE
    (access_start_datetime), U.genderdomain_id
    I get the value for the gender like
    female 105
    male 85

    how can I do for retrieve proportional value like
    female 56%
    male 44%
    ?

    Thanks in advance.
    Bye

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    I think the easiest would be to do that in your scripting language (PHP?).

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, or better php is in the middle
    (that query is done by a sort of api that return the value
    in json format and than by a xhr I retrieve it )
    is it so tricky do it in mysql ?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    To calculate the proportion, you need the total value. So you would have to something like
    Code:
    SELECT
        a.domain_id
      , a.field
      , a.date
      , a.value / b.value
    FROM
      (SELECT 
           A.domain_id
         , U.gender as field
         , DATE(access_start_datetime) as date
         , COUNT(A.id) as value
       FROM  stats_access A
       JOIN users U 
       ON U.id = A.fb_id
       GROUP BY 
           DATE(access_start_datetime)
         , U.gender
         , domain_id
      ) AS a
    INNER JOIN
      (SELECT 
           A.domain_id
         , DATE(access_start_datetime) as date
         , COUNT(A.id) as value
       FROM  stats_access A
       JOIN users U 
       ON U.id = A.fb_id
       GROUP BY 
           DATE(access_start_datetime)
         , domain_id
      ) AS b
    ON a.domain_id = b.domain_id
    AND a.value = b.value
    The first subquery is your query. The second does the same thing, but doesn't group by gender, so it gives you the total needed to calculate the proportions.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code:
    AND a.value = b.value
    Code:
    AND a.date = b.date
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks buddies awsome


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
  •