Divide two values in aggregation functions?

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 result by 3 decimal places. If the result is coming as 0 then it should show only 0.

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

week    ratio
---------------------
45      0.054
46      0.345
47      0.224
48      0.456

So I tried like this and it does the job but do I need to calculate the percentage first and then divide by 100 to get what I need? I do think this can be improved so that I should not calculate percentage first. I should be able to get the result as it is without doing percentage.

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

Is this possible to do by any chance?

Answer

You can simplify the ratio calculation by just using 1.0:

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

Or, more simply:

select week, avg( (colf = 'TEMP')::int ) as ratio
from process 
where colf in ('PART', 'TEMP')
group by week;

Here is a db<>fiddle (using Postgres).

Advertisement

Answer