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

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

CodePudding user response：

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

CodePudding user response：

select country, count(ID), sum(case when type="delete" then 1 end)/count(type) from table group by country

CodePudding user response：

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;

CodePudding user response：

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