How to join three tables and count the record with UNION one column

Question

I want to join these tables and count if the Status is 'Y'

table1(date, status)
8/23/2015    Y
8/24/2015    Y
8/24/2015    N

table2(date, status)
8/23/2015    Y
8/23/2015    Y

table3(date, status)
8/23/2015    Y
8/25/2015    N
8/25/2015    Y

The result I expect is like . . .

DATE       count(table1.status)  count(table2.status)  count(table3.status)
---------  --------------------  --------------------  --------------------
8/23/2015  1                     2                     1
8/24/2015  1                     0                     0
8/25/2015  0                     0                     1

Answer 1

this is playful question.

to get the result we can sum 'Y' value after filtering it by using decode function as following.

create table table1(date1 date, status varchar2(2)) 
insert into table1 values ( to_date( '8/23/2015', 'mm/dd/yyyy'), 'Y');
insert into table1 values ( to_date( '8/24/2015', 'mm/dd/yyyy'), 'Y');
insert into table1 values ( to_date( '8/24/2015', 'mm/dd/yyyy'), 'N');

create table table2(date1 date, status varchar2(2))
insert into table2 values ( to_date( '8/23/2015', 'mm/dd/yyyy'), 'Y');
insert into table2 values ( to_date( '8/23/2015', 'mm/dd/yyyy'), 'Y');

create table table3(date1 date, status varchar2(2))
insert into table3 values ( to_date( '8/23/2015', 'mm/dd/yyyy'), 'Y');
insert into table3 values ( to_date( '8/25/2015', 'mm/dd/yyyy'), 'N');
insert into table3 values ( to_date( '8/25/2015', 'mm/dd/yyyy'), 'Y');

---

select date1,
       sum(decode(x, '1', status, null)), -- table1:x=1
       sum(decode(x, '2', status, null)), -- table2:x=2
       sum(decode(x, '3', status, null))  -- table3:x=3
  from (select 1 x, date1, decode(status, 'Y', 1, 0) status
          from table1
        union all
        select 2 x, date1, decode(status, 'Y', 1, 0) status
          from table2
        union all
        select 3 x, date1, decode(status, 'Y', 1, 0) status
          from table3)
 group by date1
 order by 1

In @Gordon Linoff solution he creates 3 columns (status1, status2, status3) to get the results, in this solution I get the result directly from data.

all the road lead to Rome.

Answer 2

Check this as well

select  t1.DATE,
SUM(NVL(decode(t1.status, 'Y', 1, 0)),0) table1_sum,
SUM(NVL(decode(t2.status, 'Y', 1, 0)),0) table2_sum,
SUM(NVL(decode(t3.status, 'Y', 1, 0)),0) table3_sum
from table1 t1,
join table2 t2,
join table3 t3,
where t1.Date = t2.date and t1.Date = t3.date
group by t1.DATE
order by t1.DATE

Answer 3

Here's another alternative:

with table1 as (select to_date('23/08/2015', 'dd/mm/yyyy') dt, 'Y' status from dual union all
                select to_date('24/08/2015', 'dd/mm/yyyy') dt, 'Y' status from dual union all
                select to_date('25/08/2015', 'dd/mm/yyyy') dt, 'N' status from dual),
     table2 as (select to_date('23/08/2015', 'dd/mm/yyyy') dt, 'Y' status from dual union all
                select to_date('23/08/2015', 'dd/mm/yyyy') dt, 'Y' status from dual union all
                select to_date('26/08/2015', 'dd/mm/yyyy') dt, 'Y' status from dual),
     table3 as (select to_date('23/08/2015', 'dd/mm/yyyy') dt, 'Y' status from dual union all
                select to_date('25/08/2015', 'dd/mm/yyyy') dt, 'Y' status from dual union all
                select to_date('25/08/2015', 'dd/mm/yyyy') dt, 'N' status from dual)
select coalesce(t1.dt, t2.dt, t3.dt) dt,
       coalesce(t1.cnt, 0) t1_cnt,
       coalesce(t2.cnt, 0) t2_cnt,
       coalesce(t3.cnt, 0) t3_cnt
from   (select dt, count(case when status = 'Y' then 1 end) cnt
        from table1
        group by dt) t1
       full outer join (select dt, count(case when status = 'Y' then 1 end) cnt
                        from table2
                        group by dt) t2 on t1.dt = t2.dt
       full outer join (select dt, count(case when status = 'Y' then 1 end) cnt
                        from table3
                        group by dt) t3 on t1.dt = t3.dt
order by coalesce(t1.dt, t2.dt, t3.dt);


DT             T1_CNT     T2_CNT     T3_CNT
---------- ---------- ---------- ----------
23/08/2015          1          2          1
24/08/2015          1          0          0
25/08/2015          0          0          1
26/08/2015          0          1          0

(It aggregates the rows down to one row per dt before joining to the other tables, so you don't get duplicated rows included in the counts).

Answer 4

Perhaps the easiest way is to union all the tables together and then aggregation:

select date, sum(status1) as status1, sum(status2) as status2,
       sum(status3) as status3
from ((select date, 1 as status1, 0 as status2 , 0 as status3
       from table1
       where status = 'Y') union all
      (select date, 0 as status1, 1 as status2 , 0 as status3
       from table2
       where status = 'Y') union all
      (select date, 0 as status1, 0 as status2 , 1 as status3
       from table3
       where status = 'Y') 
     ) t
group by date
order by date;

If you want to do this with full join, you have to be very careful. You are tempted to write:

select date,
       sum(case when t1.status1 = 'Y' then 1 else 0 end) as status1, 
       sum(case when t2.status1 = 'Y' then 1 else 0 end) as status2, 
       sum(case when t3.status1 = 'Y' then 1 else 0 end) as status3 
from table1 t1 full join
     table2 t2
     using (date) full join
     table3 t3
     using (date)
group by date
order by date;

But this has problems when there are multiple counts on the same date in different tables (a cartesian product for the date). So, the next temptation is to add count(distinct) . . . you can do that in this case, because there is no unique column. Even if there were, this adds overhead.

Finally, you can solve this by pre-aggregating each table, if you want to go down this path.