Postgres: group query

Question

I got the following table schema with some sample data:

country | month | code | code_count
US        1       A1     2
US        1       A2     7
US        1       A3     3
US        2       B1     7
US        2       B2     9
US        2       B3     4
US        3       C1     3
US        3       C2     6
US        3       C3     1

I need a query that will count code_count percentage that is grouped by country and month. I expect the following output:

country | month | code | code_count | percentage
US        1       A1     2            16.7 -- 2 / (2 + 7 + 3)
US        1       A2     7            58.7 -- 7 / (2 + 7 + 3)
US        1       A3     3            25.0 -- 3 / (2 + 7 + 3)
US        2       B1     7            35.0 -- 7 / (7 + 9 + 4)
US        2       B2     9            45.0 -- 9 / (7 + 9 + 4)
US        2       B3     4            20.0 -- 4 / (7 + 9 + 4)
US        3       C1     3            30.0 -- 3 / (3 + 6 + 1)
US        3       C2     6            60.0 -- 6 / (3 + 6 + 1)
US        3       C3     1            10.0 -- 1 / (3 + 6 + 1)

Answer 1

Use SUM OVER() to find the total sum of code_count in each month

Then divide the code_count by total sum of each month

select country , month , code , code_count ,
       (code_count / sum(code_count) over(partition by month)) * 100 as percentage
From youtable

Answer 2

select t.country, t.month, t.code, t.code_count,
100 * t.code_count / cast( x.scc as float) as percentage
from t
left join
(
select country, month, sum(code_count) as scc
from t
group by country, month
) x
on x.month = t.month

Grouping by country and month in the inner query and using the sum per group to find the percentage in the outer query.

SQL Fiddle : http://sqlfiddle.com/#!15/f4b5f/16