how to write an oracle single query to get data from 2 tables

Question

My system has two tables as below

I need to write a single query in oracle that gives me below result

i understand how to write them in a different query, but in a single query, I have no idea..

here the result is group by craft and planned cost=sum(hr*rate) similarly actual cost

Is `WONUM` unique in both tables, or can you have multiple `LABTRANS` for a `WPLABOR`? Or, is the combination of `WONUM` and `CRAFT` unique? (Could you have another row for 50028507 and some other craft? It seems odd that you store the same `WONUM`/`CRAFT` combinations in two places.) By the way, I recommend using more meaningful names for database objects. — William Robertson, Feb 09 '20 at 12:28
If the question is how to do joins, there are a number of tutorials e.g. [Oracle-Base: SQL for beginners](https://oracle-base.com/articles/misc/sql-for-beginners-joins). — William Robertson, Feb 09 '20 at 12:42
Dear William, actually you are right,,,there can be multiple enteries for womum for different crafts — Nisha Verma, Feb 10 '20 at 07:10

score 1 · Answer 1 · answered Feb 09 '20 at 12:39

For this sample data you can join the tables and aggregate:

select w.craft,
  sum(w.planned * w.rate) "Total Planned (cost)",
  sum(l.actual * l.rate) "Total Actual (cost)"
from wplabor w inner join labtrans l
on l.wonum = w.wonum
group by w.craft

Maybe the condition of the ON clause should include equality of the craft columns also:

on l.wonum = w.wonum and l.craft = w.craft

but for this sample data it does not seem to be needed.

score 1 · Answer 2 · answered Feb 09 '20 at 12:51

One approach is to aggregate before joining:

select p.craft, p.planned, a.actual
from (select craft, sum(hrs * rate) as planned
      from wplabor
      group by craft
     ) p join
     (select craft, sum(hrs * rate) as actual
      from labtrans
      group by craft
     ) a 
     on p.craft = a.craft;

In your example, all craft are in both tables. If some are missing from either table, you will want an outer join.

Omari Victor Omosa · Answer 3 · 2020-02-10T09:05:14.433

Also you may try this solution. This should work even when some craft are missing from either table

with
WPLABOR (WONUM, CRAFT,  HRS, RATE) as (
    select 50028507, 'AIRSYS', 0.5, 30  from dual union all
    select 50028537, 'AIRSYS', 2, 30 from dual union all
    select 50031118, 'AIRSYS', 8, 10 from dual union all
    select 50031118, 'ELEC', 8, 30 from dual union all
    select 50034485, 'ELEC', 0.5, 18 from dual  
) ,
LABTRANS  (WONUM, CRAFT,  HRS, RATE) as (
    select 50028507, 'AIRSYS', 0.5, 30  from dual union all
    select 50028537, 'AIRSYS', 1, 36 from dual union all
    select 50031118, 'AIRSYS', 6, 30 from dual union all
    select 50031118, 'ELEC', 8, 30 from dual  union all
    select 50034485, 'ELEC', 1, 17 from dual  
) 
SELECT WPLABOR.CRAFT||LABTRANS.CRAFT CRAFT,   
SUM(WPLABOR.HRS*WPLABOR.RATE)  Total_Planned_cost  , 
SUM(LABTRANS.HRS*LABTRANS.RATE) Total_actual_cost  FROM WPLABOR 
 full join
     LABTRANS
     on 1 = 0
GROUP BY WPLABOR.CRAFT||LABTRANS.CRAFT

db<>fiddle db fiddle link

Therefore assuming your your table names are WPLABOR and LABTRANS respectively. and column names as i have used/assumed. then the query can do the job. you only need this part of the query.

SELECT WPLABOR.CRAFT||LABTRANS.CRAFT CRAFT,   
SUM(WPLABOR.HRS*WPLABOR.RATE)  Total_Planned_cost  , 
SUM(LABTRANS.HRS*LABTRANS.RATE) Total_actual_cost  FROM WPLABOR 
 full join
     LABTRANS
     on 1 = 0
GROUP BY WPLABOR.CRAFT||LABTRANS.CRAFT

how to write an oracle single query to get data from 2 tables

3 Answers3