CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloperprimavera
Nick
Nick

Reputation: 137

Oracle SQL count same field across multiple tables

I have an oracle database that has the same name field in multiple tables. It kind of looks like this:

table1     table2     table3     table4
field      field      field      field

The common field in each table can either be 'yes', 'no', or null. I'm trying to get the value counts of all the fields in one query, but I can't figure it out. Basically I want this:

field     table1_cnt     table2_cnt     table3_cnt     table4_cnt
yes       20             25             30             35
no        35             25             15             5
null      8              6              7              5

I have this so far, but it only really works for one table, not multiple.

select field, count(*) as table1_cnt
from table1
group by field
_____________________________________
field     table1_cnt
yes       20
no        35
null      8

Upvotes: 1

Views: 406

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think I would recommend using union all and aggregate:

select field,
       sum(table_1), sum(table_2), sum(table_3), sum(table_4)
from ((select field, 1 as table_1, 0 as table_2, 0 as table_3, 0 as table_4 from table1) union all
      (select field, 0, 1, 0, 0 from table2) union all
      (select field, 0, 0, 1, 0 from table3) union all
      (select field, 0, 0, 0, 1 from table4) 
     ) t
group by field;

This has three advantages over using left joins:

  1. All field values are included in the result, not just the values in the first table.
  2. NULL values are included in the result.
  3. 0 values are included, where appropriate (rather than NULL counts).

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try using join

select t1.field,table1_cnt,table2_cnt,table3_cnt,table4_cnt
from
(
select field, count(*) as table1_cnt
from table1
group by field
)t1 left join 
(
select field, count(*) as table2_cnt
from table2
group by field
)t2 on t1.field=t2.field left join
(
select field, count(*) as table3_cnt
from table3
group by field
)t3 on t1.field=t3.field left join
(
select field, count(*) as table4_cnt
from table4
group by field
)t2 on t1.field=t4.field

Upvotes: 1

Related Questions