CODEWITHSUNDEEP
sqlpostgresql
Mandibajr
Mandibajr

Reputation: 27

Calculating Percentage of values with 0 grouped by another column

I am trying to calculate the percentage of teams with 0 as a value for each month. My current table looks similar to this

+-------------+---------------+-----------+
date            team             Value
+-------------+---------------+-----------+
01/01/2018        sales             0
02/01/2018        engineering       3
03/01/2018        Sales             3
04/01/2018        executives        0
01/02/2018        sales             3
02/02/2018        engineering       0
03/02/2018        executives        0
04/02/2018        engineering       2
01/03/2018        sales             5
02/03/2018        engineering       0
03/03/2018        executives        3
04/03/2018        Sales             2

and I already tried the query to_char(date,'YYYY')||''|| to_char(date,'Month') as month,team,100*SUM(CASE WHEN Value = 0 THEN 1 ELSE 0 END)/COUNT(Value). from table but that doesn't give the desired output.

my desired output should look similar to this


+-------------+---------------+----------------------+---------------+
month           Sales            engineering           executives      
+-------------+---------------+----------------------+---------------+
01/2018           50%            0%                     50%     
02/2018            0%            50%                    50%     
03/2018            0%            100%                     0%

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I prefer to keep the month column as a date, so I would use:

select date_trunc(month, date) as yyyymm,
       sum(val) filter (where team = 'sales') * 1.0 / sum(val) as sales,
       sum(val) filter (where team = 'engineering') * 1.0 / sum(val) as engineering,
       sum(val) filter (where team = 'executives') * 1.0 / sum(val) as executives
from t
group by yyyymm;

If you really want a string, you can use something like to_char(date, 'YYYY-MM') or whatever format you prefer. Only one expression is needed.

EDIT:

The above solves the wrong problem! I thought it was a proportion of values. But the proportion that are 0:

select date_trunc(month, date) as yyyymm,
       avg( (val = 0)::int ) filter (where team = 'sales') as sales,
       avg( (val = 0)::int ) filter (where team = 'engineering') as engineering,
       avg( (val = 0)::int ) filter (where team = 'executives') as executives
from t
group by yyyymm;

Upvotes: 1

Related Questions