How to calculate percentage by count of column values group by week?

user_14431930 0
Viewed 64

I have a table view like this:

week    ColB    ColF
---------------------
45      1234    PART
45      8215    TEMP
45      2834    PART
45      4152    PART
45      5775    TEMP
45      6527    PART
45      1162    TEMP
45      9154    
45      2162    
46      4232    PART
46      3215    PART
46      5834    PART
46      6152    PART
46      7775    TEMP
46      8527    PART
46      9162    TEMP
46      2354    
46      2562    
46      9762    
...
...
...

Now for each week I need to do below things:

  • For each week, how many total PART and TEMP are there. Let's call this total. In ColF I can have other values as well apart from PART and TEMP like empty string or any other values. I just need to count PART and TEMP only.
  • For each week, how many TEMP are there. Let's call this temp.
  • For each week, divide temp/total and get the percentage.

So output should be like this. Basically group by on week column.

week    ratio
---------------------
45      5.5%
46      4.3%
47      2.3%
48      1.7%

I was trying like this but somehow not understanding fully on how to achieve above result? My below query is wrong -

select week, (SELECT week, count(*) FROM process WHERE ColF = 'TEMP' group by week) / count(*)
from process where ColF IN ('PART', 'TEMP')
group by week
order by week

Is this possible to do by any chance?

amazon-redshift aggregate-functions pivot average sql
1 Answers

You can use conditional aggregation. avg() comes handy to compute the ratio:

select week, avg(case when colf = 'TEMP' then 100.0 else 0 end) as ratio
from process 
where colf in ('PART', 'TEMP')
group by week
user_106767160
Related