getting percentage of columns based on >1 column

Question

I have a schema like this returned from a query

+------------+-------------+------------+---------+------+
| dt         | country     | city       | gender  | cnt  |
+------------+-------------+------------+---------+------+
| 2016-07-06 | algeria     | a1         | female  |  187 |
| 2016-07-06 | algeria     | a1         | gents   | 1045 |
| 2016-07-06 | algeria     | a2         | female  |  212 |
| 2016-07-06 | algeria     | a2         | gents   |  775 |
+------------+-------------+------------+---------+------+

query which results the above query :

select DATE(created) as dt,country, city, gender, count(gender) as cnt 
from my_table 
where created>'2016-07-06 00:00:00'
group by dt,country,city, gender

expected result (female ratio in last column on per county per city basis)

+------------+-------------+------------+----------+
| dt         | country     | city       | f_ratio  |
+------------+-------------+------------+----------+
| 2016-07-06 | algeria     | a1         |  0.151   |
| 2016-07-06 | algeria     | a2         |  0.214   |
+------------+-------------+------------+----------+

i tried this :

select dt as date, (cnt / sum(cnt) * 100) as f_ratio from (
  select DATE(created) as dt, gender, count(gender) as cnt 
  from count_table 
  where created>'2016-07-06 00:00:00'
  group by dt, gender
) as inner_t
group by dt;

but it fails miserably when it comes to calculating percentage on the basis of 2 columns.

Can someone please help?

Maybe this will help you: https://stackoverflow.com/questions/15746749/mysql-calculate-percentage — andreini, Jul 08 '16 at 12:11
@niandrei : have checked all possible mysql percentage based related questions on SO..not working buddy!! :'( — NoobEditor, Jul 08 '16 at 12:13
This might be faster if it directly assessed the core tables. — Strawberry, Jul 08 '16 at 12:32

Vignesh Kumar A · Accepted Answer · 2016-07-08T12:57:40.193

1

May be something like this

SELECT T.dt as date,T.country,T.City,T.cnt / S.Tot AS f_ratio  
FROM (
       SELECT DATE(created) as dt,country, city, gender, count(gender) as cnt 
       FROM my_table 
       WHERE created>'2016-07-06 00:00:00'
       GROUP BY dt,country,city, gender
     ) T 
 JOIN
     (
      SELECT city,SUM(cnt) as Tot
      FROM (
         SELECT DATE(created) as dt,country, city, gender, count(gender) as cnt 
         FROM my_table 
         WHERE created>'2016-07-06 00:00:00'
         GROUP BY dt,country,city, gender
           ) X
      GROUP BY X.city
) AS S ON S.City =T.City AND T.gender  = 'female'

SQL FIDDLE

edited Jul 08 '16 at 12:57

answered Jul 08 '16 at 12:14

Vignesh Kumar A

27,863
13
63
115

`count_table` in itself is result of another `select` query, do i need to replace `count_table` with `select` statement? – NoobEditor Jul 08 '16 at 12:25
so this `schema` that i have mentioned above is a result of a `select` query.On this select result i have to calculate out the required data.I tried replacing the `count_table` with my `select` statement but its throwing errors!! :\ – NoobEditor Jul 08 '16 at 12:31
Updated answer, which is little bit lengthy – Vignesh Kumar A Jul 08 '16 at 12:57

getting percentage of columns based on >1 column

1 Answers1