Get proportional value for a value for a pie chart

Databases
1

Hi,
with this query


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;

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

I think the easiest would be to do that in your scripting language (PHP?).

3

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

To calculate the proportion, you need the total value. So you would have to something like


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 
AND a.[COLOR="#0000FF"]date [/COLOR]= b.[COLOR="#0000FF"]date[/COLOR]
6

thanks buddies awsome :slight_smile: