How to group a view by the tables from which it gathers its data

oracle

I have four different tables, let's say "LAMPS", "CARS", "DRONES" and "OVENS". Each of these tables has a column called "STATUS", which has a value of either 'ON' or 'OFF'. I want to create a view that show the data from all these four tables.

I then want to filter this view by the "STATUS" column. I am interested in only seeing the entries with the 'OFF' value.

Finally, I want to group the results by the table from which it came. I'm thinking of creating a new column in the view that would get the original_table_name for each of the rows and group by that value.

+------------+------------+-----------------+--------+
| TABLE_NAME |  SOMECOL1  |    SOMECOL2     | STATUS |
+------------+------------+-----------------+--------+
| LAMPS      | somevalue1 | someothervalue1 | OFF    |
| CARS       | somevalue2 | someothervalue2 | OFF    |
+------------+------------+-----------------+--------+

This is what I have so far:

CREATE VIEW my_view 
  AS SELECT * FROM LAMPS l, CARS c, DRONES d, OVENS s WHERE l.STATUS = 'OFF' OR c.STATUS = 'OFF' OR d.STATUS = 'OFF' OR s.STATUS = 'OFF';

Without modifying any of the original tables, how can I group a multi-table view by the tables from which it gathers its results?

Best Answer

This is how I usually do that: view is UNION (ALL) of all my tables. I include an origin column which shows where those rows come from.

I wouldn't restrict rows to status = off at this stage; tomorrow you'll want to check ons so - what, will you recreate the view? Let it be.

create or replace view my_view as
  select 'L' origin, l.id, l.name, l.status, ... from lamps l
  union all
  select 'C' origin, c.id, c.name, c.status, ... from cars c
  union all
  select 'D' origin, d.id, d.name, d.status, ... from drones d
  union all
  select 'O' origin, o.id, o.name, o.status, ... from ovens o;

Then, it is a matter of a simple counting:

select v.origin, count(*) cnt
from my_view v
where v.status = 'OFF'
group by v.origin;
Related Question