Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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  
                @Nick . . . This does not work if the first table does not have all the values in the subsequent tables.  In addition, I don't think it counts NULL values correctly.
– Gordon Linoff
                Oct 13, 2020 at 12:08
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) 
group by field;
This has three advantages over using left joins:
All field values are included in the result, not just the values in the first table.
NULL values are included in the result.
0 values are included, where appropriate (rather than NULL counts).
                For my particular case I don't care about NULL or 0 values. These are values entered into the database by a form radio button and they are guaranteed to be 'Yes' or 'No'. There just happens to be multiple forms, with the same inputs, each with their own database table.
– Nick
                Nov 17, 2020 at 9:44
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.