Oracle SQL join and expand [closed]

I have 2 tables – sales and ratio.

sales table has 2 columns – state, sales. This has total sales by state.

ratio table has 3 columns – state, type, pct. This has the % of each type of sales in a state (online, store, other). For each state, total of pct is 1 (100%).

How do I best get a result table which has these columns – state, type, sales?

Thanks!

Sample data: Sales:

|State|Sales|
|:-----|-----:|
|AL|100|
|AK|500|

Ratio:

|State|Type|Ratio|
|:-----|:----|-----:|
|AL|Online|0.4|
|AL|Store|0.5|
|AL|Other|0.1|
|AK|Online|0.8|
|AK|Store|0.1|
|AK|Other|0.1|

Result:

|State|Type|Sales|
|:-----|:----|-----:|
|AL|Online|40|
|AL|Store|50|
|AL|Other|10|
|AK|Online|400|
|AK|Store|50|
|AK|Other|50|

Answer

Join, as you said.

SQL> with
  2  -- Sample data; don't type that
  3  sales (state, sales) as
  4    (select 'AL', 100 from dual union all
  5     select 'AK', 500 from dual
  6    ),
  7  ratio (state, type, ratio) as
  8    (select 'AL', 'Online', 0.4 from dual union all
  9     select 'AL', 'Store' , 0.5 from dual union all
 10     select 'AL', 'Other' , 0.1 from dual union all
 11     --
 12     select 'AK', 'Online', 0.8 from dual union all
 13     select 'AK', 'Store' , 0.1 from dual union all
 14     select 'AK', 'Other' , 0.1 from dual
 15    )
 16  -- Query you might be interested in begins here
 17  select r.state,
 18    r.type,
 19    s.sales * r.ratio as sales
 20  from sales s join ratio r on r.state = s.state
 21  order by r.state desc,
 22           r.type;

ST TYPE        SALES
-- ------ ----------
AL Online         40
AL Other          10
AL Store          50
AK Online        400
AK Other          50
AK Store          50

6 rows selected.

SQL>