I have the following data (actually spans over years but for this example, I only included 4 months)
id created_at staff
--------------------------------
1 2010-01-01 Coder
2 2010-01-15 Developer
3 2010-03-01 Data Analyst
4 2010-01-20 Developer
5 2010-03-13 Data Analyst
6 2010-04-05 Tester
7 2010-04-01 Tester
8 2010-04-04 Business Analyst
9 2010-01-22 Business Analyst
10 2010-01-25 Coder
I then use the following query to count the staff by months.
select staff,
sum(case when date_trunc('month', created_at) = date '2010-01-01' then 1 else 0 end) as cnt_2010_01,
sum(case when date_trunc('month', created_at) = date '2010-02-01' then 1 else 0 end) as cnt_2010_02,
sum(case when date_trunc('month', created_at) = date '2010-03-01' then 1 else 0 end) as cnt_2010_03,
sum(case when date_trunc('month', created_at) = date '2010-04-01' then 1 else 0 end) as cnt_2010_04
from mytable
group by staff
Below is an example of the output that the above SQL produces.
staff 2010-01 2010-02 2010-03 2010-04
----------------------------------------------------------
Coder 2 0 0 0
Developer 2 0 0 0
Data Analyst 0 0 2 0
Tester 0 0 0 2
Business Analyst 1 0 0 1
I am now trying to count the number of columns that have a value. The expected outcome I am looking for is
staff months 2010-01 2010-02 2010-03 2010-04
--------------------------------------------------------------------------
Coder 1 2 0 0 0
Developer 1 2 0 0 0
Data Analyst 1 0 0 2 0
Tester 1 0 0 0 2
Business Analyst 2 1 0 0 1
you need to use count(distinct) as well:
select staff,
count(distinct date_trunc('month', created_at)) as months
sum(case when date_trunc('month', created_at) = date '2010-01-01' then 1 else 0 end) as cnt_2010_01,
sum(case when date_trunc('month', created_at) = date '2010-02-01' then 1 else 0 end) as cnt_2010_02,
sum(case when date_trunc('month', created_at) = date '2010-03-01' then 1 else 0 end) as cnt_2010_03,
sum(case when date_trunc('month', created_at) = date '2010-04-01' then 1 else 0 end) as cnt_2010_04
from mytable
group by staff