SQL select query using Joins with aggregate counts-CodePudding

I have a table with the following fields:

tickets:  id, createddate, resolutiondate

A sample set of data has:

jira=# select * from tickets;
   id    | createddate | resolutiondate
--------- ------------- ----------------
 ticket1 | 2020-09-21  | 2020-10-01
 ticket2 | 2020-09-22  | 2020-09-23
 ticket3 | 2020-10-01  |
 ticket4 | 2020-10-01  | 2020-10-04
 ticket5 | 2020-10-01  |
 ticket6 | 2020-10-01  | 2020-10-07
(6 rows)

jira=#

I would like to create a query which reports:

Week:  Issues Created:  Issues Resolved

I can do the two separate queries:

# select date_trunc('week', createddate) week, count(id) created
    from tickets
    group by week
    order by week desc
    ;
          week          | created
------------------------ ---------
 2020-09-28 00:00:00 00 |       4
 2020-09-21 00:00:00 00 |       2
(2 rows)

# select date_trunc('week', resolutiondate) week, count(id) resolved
    from tickets
    where resolutiondate is not NULL
    group by week
    order by week desc
    ;

          week          | resolved
------------------------ ----------
 2020-10-05 00:00:00 00 |        1
 2020-09-28 00:00:00 00 |        2
 2020-09-21 00:00:00 00 |        1
(3 rows)

However - I can not figure out how (with a join, union, sub-query, ...?) to combine these queries into a combined query with the appropriate aggregations.

I'm doing this is Postgres - any pointers would be appreciated.

CodePudding user response：

Performing a union before aggregating values may work here eg

select week, 
       count(id_created) as created,
       count(id_resolved) as resolved
from (
    select date_trunc('week', resolutiondate) week, NULL as id_created, id as id_resolved from tickets UNION ALL
    select date_trunc('week', createddate) week, id as id_created, NULL as id_resolved from tickets
) t
    group by week
    order by week desc

Let me know if this works for you.