All Forums Database
divya12 1 post Joined 03/12
29 Mar 2012
Count by two different columns without using full outer join


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


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

join cal_table on

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

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?



ulrich 816 posts Joined 09/09
29 Mar 2012

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
group by 1


feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.