All Forums Database
divya12 1 post Joined 03/12
29 Mar 2012
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?

 

 

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
        join 
        cal_table 
                on table_a.closed_date=cal_table.date
                     or table_a.created_date=cal_table.date
group by 1

?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.