CODEWITHSUNDEEP
sqlpostgresql
Eduardo Kaneko
Eduardo Kaneko

Reputation: 53

How can I count the occurrences of two date columns and show it in only one table?

I'm building a query that aims to show the number of occurrences of two date variables per month. I was able to assemble the two separate queries: I count the number of occurrences and group per month, but I have no idea how to join these two queries, since they are from the same table, and still show the count with only one column of month.

Thanks for your help, guys!

Format: YYYY-MM-DD

|---------------------|------------------|
|   onboard_date      | offboard_date    |
|---------------------|------------------|
|    2019/01/15       | -                |
|---------------------|------------------|
|    2019/01/25       |    2019/02/15    |
|---------------------|------------------|
|    2019/02/13       |    2019/02/20    | 
|---------------------|------------------|
|    2019/02/18       | -                |
|---------------------|------------------|
|    2019/03/09       | -                |
|---------------------|------------------|

What I have tried and worked: 

SELECT DATE_TRUNC('month', onboard_date) AS onboard_month,
        COUNT(*) as onboards
FROM lukla.trn_users trn
WHERE trn.company_name = 'amaro'
GROUP BY DATE_TRUNC('month', onboard_date)
ORDER BY DATE_TRUNC('month', onboard_date)

and 

SELECT DATE_TRUNC('month', offboard_date) AS onboard_month,
        COUNT(*) as onboards
FROM lukla.trn_users trn
WHERE trn.company_name = 'amaro' AND offboard_date IS NOT NULL 
GROUP BY DATE_TRUNC('month', offboard_date)
ORDER BY DATE_TRUNC('month', offboard_date)

The result that I want:


|--------------|------------|------------|
|    month     | onboards   | offboards  |
|--------------|------------|------------|
|    01        |      2     |      0     |
|--------------|------------|------------|
|    02        |      2     |     2      |
|--------------|------------|------------|
|    03        |      1     |      0     |
|--------------|------------|------------|

Upvotes: 1

Views: 221

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

A lateral join makes this pretty simple:

select date_trunc('month', v.dte) as month, sum(v.is_onboard) as onboards, sum(v.is_offboard) as offboards
from trn_users t cross join lateral
     (values (t.onboard_date, (t.onboard_date is not null)::int, 0),
             (t.offboard_date, 0, (t.offboard_date is not null)::int)
     ) v(dte, is_onboard, is_offboard)
where v.dte is not null
group by month
order by month;

Here is a db<>fiddle.

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

You can try to full join two derived tables, one getting the count for on boards, the other the count for off boards.

SELECT coalesce(x.month, y.month) month,
       coalesce(x.count, 0) onboards,
       coalesce(y.count, 0) offboards
       (SELECT date_trunc('month', trn.onboard_date) month
               count(*) count
               FROM lukla.trn_users trn
               WHERE trn.company_name = 'amaro'
                     AND trn.onboard_date IS NOT NULL
               GROUP BY date_trunc('month', trn.onboard_date)) x
       FULL JOIN (SELECT date_trunc('month', trn.offboard_date) month
                         count(*) count
                         FROM lukla.trn_users trn
                         WHERE trn.company_name = 'amaro'
                               AND trn.offboard_date IS NOT NULL
                         GROUP BY date_trunc('month', trn.offboard_date)) y
                 ON y.month = x.month
       ORDER BY coalesce(x.month, y.month);

Upvotes: 0

Related Questions