Search code examples
mysqlsqlsumsubquerywindow-functions

Proportion in SQL

My table is like this

C1   Bool    Count

A    TRUE     5
A    FALSE    5
B    FALSE    6
C    TRUE     2
C    FALSE    8

I want to calculate the portion of count for each type of Bool, group by C1. The ideal result should be

C1   Bool    Portion

A    TRUE     0.5
A    FALSE    0.5
B    FALSE    1
C    TRUE     0.2
C    FALSE    0.8

I am not really sure how to achieve this. Really appreciate any help

Solution

  • This is pretty similar to your previous question, but without aggregation.

    If you are running MySQL 8.0, you can use window functions:

    select t.*, count / sum(count) over(partition by c1) ratio
from mytable t

    In earlier versions, an alternative is a join or correlated subquery:

    select t.*, count / (select sum(t1.count) from mytable t1 where t1.c1 = t.c1) ratio
from mytable t