combine outputs of two queries group by a common field

database, oracle, sql

Solution

Looking at your desired result you need a `JOIN` rather then a `UNION`. You can do it like this

select coalesce(odate, cdate) odate, count1, sum1, count2, sum2
  from
(
  select odate, count(odate) count1, sum(dur) sum1
    from table1
   group by odate
) t1 full join
(
  select cdate, count(cdate) count2, sum(dur) sum2
    from table2
   group by cdate
) t2
    on t1.odate = t2.cdate
 order by odate;

Sample output:

|                          ODATE | COUNT1 |   SUM1 | COUNT2 |   SUM2 |
|--------------------------------|--------|--------|--------|--------|
| January, 01 2013 00:00:00+0000 |      2 |     30 |      2 |     30 |
| January, 02 2013 00:00:00+0000 |      1 |     30 | (null) | (null) |
| January, 03 2013 00:00:00+0000 | (null) | (null) |      1 |     30 |

Here is SQLFiddle demo

Problem

I want to combine the results of these two queries: ``` SELECT odate, Count(odate), Sum(dur) FROM table1 t1 GROUP BY odate ORDER BY odate; SELECT cdate, Count(cdate), Sum(dur) FROM table2 t2 GROUP BY cdate ORDER BY cdate; ``` and get something like this as a result: ``` odate,t1.count(odate),t2.sum(dur),t2.count(cdate),t2.sum(dur) order by odate ``` how to do that? I get an error when I run this one: ``` select odate,count(odate),sum(dur) from table1 t1 group by odate order by odate union select cdate,count(cdate),sum(dur) from table2 t2 group by cdate order by cdate; ```

Original source