Question 283558 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Сombining date columns into one

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:

enter image description here

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.

Tags:

2

Answers


  1. 0

    FULL JOIN looks ok for the job. Minor suggestions:

    WITH sig AS (
   SELECT date_signing::date AS the_date, count(*) AS signing
   FROM   services
   GROUP  BY 1
   )
, can AS (
   SELECT date_cancellation::date AS the_date, count(*) AS cancelling
   FROM   services
   GROUP  BY 1
   )
SELECT *
FROM   sig
FULL   JOIN can USING (the_date)
WHERE  the_date IS NOT NULL;  -- optional to eliminate row(s) with null date

    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 for the_date IS NULL).

    Related:

    Login or Signup to reply.
  2. 0

    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 of null. Demo at db<>fiddle:

    select d::date, count(s.*)signing, count(c.*)cancelling 
from generate_series('2023-03-09'::timestamp,'2023-03-23','1 day')_(d)
     left join services s on d::date =s.date_signing::date
     left join services c on d::date =c.date_cancellation::date
group by 1 order by 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search