Counting Across Tables

Without losing any data

1. Use FROM clause subquery
2. UNION ALL not UNION
3. Implicit DISTINCT in UNION eliminates duplicate rows.

To count sales at two stores of a retail florist:

1. With a view:

create or replace view flower_sales
(flower_name, flower_quantity)
as
(select f1_name, f1_quantity from f1
union
select f2_name, f2_quantity from f2);

select
  flower_name "flower",
  sum(flower_quantity) "quantity"
  count(*) "count"
from flower_sales
group by flower_name
order by 1;

2. With a subquery:

select
  flower_name "flower",
  sum(flower_quantity) "quantity"
  count(*) "count"
from 
  (select f1_name, f1_quantity from f1
  union all
  select f2_name, f2_quantity from f2)
group by flower_name
order by 1;