If i have the data table below:
COLUMN 1 COLUMN2
Country1 NULL
Country1 0
Country1 NULL
Country1 0
Country1 NULL
Country1 0
Country1 NULL
Country1 18
Country1 NULL
Country1 12
Country1 NULL
Country1 0
Country1 NULL
Country1 0
Country1 NULL
Country2 0
Country2 NULL
Country2 7
Country2 NULL
Country2 0
Country2 NULL
Country2 0
Country2 NULL
Country2 0
Ignoring the rows with second column as NULL, how can I get the following values out of it:
Country1 has 7 values, out of which 5 are zero, 1 is 18 and 1 is 12. Country2 has 5 values, out of which 4 are zero, 1 is 7
How can I get a table that looks like this?
COUNTRY VALUE PERCENT
Country1 0 71.43 (i.e. 5/7)
Country1 18 14.29 (i.e. 1/7)
Country1 12 14.29 (i.e. 1/7)
Country2 0 80 (i.e. 4/5)
Country2 7 20 (i.e. 1/5)
You can do this using simple aggregation and window functions:
select country, value,
cast(count(*) as float) / sum(count(value)) over (partition by country) as ratio
from table t
group by country, value;
This gives the third column as a ratio. You can format it as a percent in various ways, such as by using str().
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments