The database has columns with the date of conclusion of the contract and the date of termination of the contract. I need to group by date the number of conclusions and the number of cancellations.
I have written queries
with conn as (select date_signing::date as dt_sign, count(date_signing) as signing from services GROUP by date_signing::date),
disconn as (select date_cancellation::date as dt_canc, count(date_cancellation) as cancelling from services GROUP by date_cancellation::date)
select * from conn full join disconn on conn.dt_sign = disconn.dt_canc
Here is the result:
But I need this result (for example):
date signing cancelling
2019-08-09 1 null
2020-05-05 null 1
I need one column with date and columns with signing and termination to see the dynamics.
Please tell me in which direction to think.
2
Answers
FULL JOIN
looks ok for the job. Minor suggestions:To simplify, use the same column alias and join with the
USING
clause to get a single output column.Also,
count(*)
is faster and equivalent for this (except for also getting the correct count forthe_date IS NULL
).Related:
If you don’t want gaps in this report, and especially if you want to zoom in on a period, generate a calendar and
left join
to it. Counts on that also yield a proper zero instead ofnull
. Demo at db<>fiddle: