How to calculate percentage of certain value in column with sql?

user_13174189 0
Viewed 233

I have a table:

country      ID            type
US           a1           upload
US           a1           delete
US           a1           delete
US           a2           upload
US           a2           upload
US           a2           delete
IT           k2           upload
IT           k2           upload
IT           k2           delete

I want to get table with unique countries, number of unique ID's in it and average percent of "delete" value in type among each ID in country. So desired table must look like:

country      ID_cnt             delete_perc
US            2                   0.5
IT            1                   0.33

Its 0.498 since for ID a1 its 0.66 and for a2 it's 0.33, s (0.66 + 0.33)/2 = 0.5

How to do that? I need it with Presto

presto sql
4 Answers

Try this

    Select country, count(distinct id), (sum(case when 
       type='delete' then 1 end) 
       /count(type) )*100 as cnt from table group by 
      country
user_104974830 
select country, count(ID), sum(case when type="delete" then 1 end)/count(type) from table group by country
user_46656840 
select country, count(distinct id), (select count(*) from tmp as tmp1 where tmp1.country=tmp.country and type='delete')::float/count(type)::float from tmp
group by country;
user_74331280

try this:

    Select country, count(distinct id) as ID_cnt, 
    round( cast(sum(case when type='delete' then 1 end) as float) 
           /cast(count(type) as float), 2) as delete_perc 
    from Table group by country

This query show percentage of exactly 2 digits

user_83062860
Related