ORACLE - Count from two tables with group by

Question

I've two tables (table1 and table2) that share a column (day_code number).

I want to obtain the count of records that each table have from a minimum day_code and group by the results by day_code.

Table 1 (number of records by day_code)

20160703 - 5
20160704 - 4

Table 2 (number of records by day_code)

20160703 - 5
20160704 - 4

I need something like that:

----------------------------------------------------
DAY_CODE | TABLE 1 | TABLE 2 |
20160703 |    5    |    5    |
20160704 |    4    |    4    |

I'm using that query:

SELECT *
FROM
(
SELECT day_code, COUNT(day_code) AS TB1 FROM TABLE1 GROUP BY day_code
UNION ALL
SELECT day_code, COUNT(day_code) AS TB2 FROM TABLE2 GROUP BY day_code
) s
where day_code between 20160703 and 20160704

I'm obtaing this:

DAY_CODE  |  TB1
20160703  |   5
20160704  |   4
20160703  |   5
20160704  |   4

Can you help me?

Thank you in advance for your advices, LR

It is not entirely clear what your output requirement is. A single, one-row string with pipe-separated values? A table of rows, with column headers DAY_CODE, TABLE 1 and TABLE 2 and one row per DAY_CODE? — user5683823
– user5683823, Commented Jul 6, 2016 at 17:59
Sorry @mathguy. But I've already what I want. kordirko answer is right. ;) — Luís Ramos
– Luís Ramos, Commented Jul 7, 2016 at 8:25
Understood. a_horse's edits helped. I offered an answer as well. Please mark kordiko's answer as "correct" (he posted his first); but after you do that, you may want to try both answers and see which works faster in your particular situation. — user5683823
– user5683823, Commented Jul 7, 2016 at 12:15

krokodilko · Accepted Answer · 2016-07-06 18:13:15Z

1

Try:

SELECT coalesce( t1.day_code, t2.day_code) As daycode,
       nvl( cnt1, 0 ) cnt1,
       nvl( cnt2, 0 ) cnt2
FROM ( 
  SELECT day_code, count(*) cnt1
  FROM tab1
  GROUP BY day_code
) t1
FULL OUTER JOIN ( 
  SELECT day_code, count(*) cnt2
  FROM tab2
  GROUP BY day_code
) t2
ON t1.day_code = t2.day_code
ORDER BY 1

answered Jul 6, 2016 at 18:13

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luís Ramos Over a year ago

Thank you @kordiko. It's perfect! ;)

krokodilko Over a year ago

I am glad that I cold help. If ypu like this answer, please accept it, thank you.

user5683823 · Accepted Answer · 2016-07-06 18:54:56Z

Here is a solution using pivot. I created more data to show proper treatment of null values.

with table1 (day_code, ct) as (
       select 20160703, 5 from dual union all
       select 20160704, 4 from dual union all
       select 20160705, 7 from dual
     ),
     table2 (day_code, ct) as (
       select 20160703, 5 from dual union all
       select 20160704, 8 from dual
     )
select *
from (select day_code, ct, 1 as t from table1
      union all
      select day_code, ct, 2 as t from table2
     )
pivot (min(ct) for t in (1 as table1, 2 as table2))
order by day_code;

Output:

  DAY_CODE     TABLE1     TABLE2
---------- ---------- ----------
  20160703          5          5
  20160704          4          8
  20160705          7

Collectives™ on Stack Overflow

ORACLE - Count from two tables with group by

Table 1 (number of records by day_code)

Table 2 (number of records by day_code)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table 1 (number of records by day_code)

Table 2 (number of records by day_code)

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related