CODEWITHSUNDEEP
sqlpostgresqldatetimesumunpivot
Israel Rodrigues
Israel Rodrigues

Reputation: 371

merge two COUNT in one SQL query

I need to do a query to show new_customers X customers_cancellations

with this query I can get the new_customers by month:

select count(start_date), to_char(start_date, 'MM') as monthNumber, to_char(start_date, 'YY') as yearNumber 
from customer where start_date is not null 
group by to_char(start_date, 'MM'), to_char(start_date, 'YY') 
order by yearNumber, monthNumber;

with this other, I can get the customers cancellations by month:

select count(cancellation_date), to_char(cancellation_date, 'MM') as monthNumber, to_char(cancellation_date, 'YY') as yearNumber
from customer where cancellation_date is not null 
group by to_char(cancellation_date, 'MM'), to_char(cancellation_date, 'YY') 
order by yearNumber, monthNumber;

the both queries return something like:

count|  monthnumber |yearnumber
1    |  1           | 20
7    |  2           | 20
5    |  3           | 20

but I would like something like this:

customer_out_count|customer_in_count| monthnumber   |yearnumber
 0                |1                |   1           | 20
 0                |7                |   2           | 20
 1                |0                |   3           | 20
 0                |1                |   4           | 20
 5                |7                |   5           | 20
 1                |5                |   6           | 20

I already tried this other query:

select 'start' as type, count(start_date) as count, to_char(start_date, 'MM') as monthNumber, to_char(start_date, 'YY') as yearNumber
from customer where start_date is not null
group by to_char(start_date, 'MM'), to_char(start_date, 'YY')
union
select 'cancellation' as type, count(cancellation_date) as counta, to_char(cancellation_date, 'MM') as monthNumber, to_char(cancellation_date, 'YY') as yearNumber
from customer where cancellation_date is not null
group by to_char(cancellation_date, 'MM'), to_char(cancellation_date, 'YY')
order by yearNumber, monthNumber;

The result it's "ok":

type                  |newcount         | monthnumber   |yearnumber
 start                |1                |   1           | 20
 cancellation         |1                |   1           | 20
 cancellation         |7                |   2           | 20
 start                |3                |   3           | 20
 cancellation         |1                |   4           | 20
 start                |7                |   5           | 20
 start                |5                |   6           | 20

But I will need to do some operations on code to achieve what I need.

How can I merge this two queries in one? I'm using postgreslq.

Upvotes: 1

Views: 54

Answers (1)

GMB
GMB

Reputation: 222482

One option unpivots the rows, then aggregate. In Postgres, you would express this with a lateral join:

select
    sum(x.customer_in_count)  as customer_in_count,
    sum(x.customer_out_count) as customer_out_count
    to_char(x.dt, 'MM') as monthNumber, 
    to_char(x.dt, 'YY') as yearNumber
from customer c
cross join lateral (values
    (c.start_date, 1, 0), (c.cancellation_date, 0, 1)
) as x(dt, customer_in_count, customer_out_count)
where x.dt is not null
group by monthNumber, yearNumber
order by monthNumber, yearNumber

Upvotes: 1

Related Questions