Count by two different columns without using full outer join

Database

Count by two different columns without using full outer join

Hi,

Need help on the following scenario.

Consider the following dataset

transaction created_date closed_date

1                1-jan-2011       5-jan-2011

2                2-jan-2011       10-jan-2011

3                5-jan-2011       5-feb-2011

4                10-jan-2011     6-mar-2011

5                 1-feb-2011       10-feb-2011

6                  3-feb-2011       10-mar-2011

7                  5-mar-2011      15-mar-2011

I want to get the distinct count of transactions created and closed in each month without using full outer join.

The resultant data set is to be as follows

month      created_count       closed_count

jan              4                             2

feb               2                            2

mar              1                            3

To get this resultset i used the following full outer join query

sel x.mnth, x.created_count,y.closed_count

from

(sel cal_table.month as mnth, count(1) as created_count from table_a

join cal_table on table_a.created_date=cal_table.date

group by 1)x

Full outer join

(sel cal_table.month as mnth1, count(1) as closed_count from table_a

join cal_table on table_a.closed_date=cal_table.date

group by 1) y on x.mnth=y.mnth1

The reason i want this without the use of full outer joins is because the counts will be split across various other parameters like source, origin etc.. If i use full outer join i have to use all those parameters in both the subqueries and finally join both the subqueries on all those parameters. 

Can anyone please suggest somethign here?

1 REPLY
Supporter

Re: Count by two different columns without using full outer join

How about 

sel cal_table.month as mnth, 
sum(case when extract(month from created_date = cal_table.month then 1 else 0 end),
sum(case when extract(month from closed_date = cal_table.month then 1 else 0 end),
from table_a
join
cal_table
on table_a.closed_date=cal_table.date
or table_a.created_date=cal_table.date
group by 1

?