CODEWITHSUNDEEP
sqloracle-database
Chris Weltes
Chris Weltes

Reputation: 3

Joining values from two tables and grouping its values

So i have this two tables,

TABLE1,

NAME  COMPONENT QUANTITY STATUS
NAME1 COLUMN    10       READY
NAME2 COLUMN    20       READY
NAME3 COLUMN    15       NOTREADY
NAME4 COLUMN    10       READY
NAME5 BEAM      20       NOTREADY
NAME6 BEAM      15       NOTREADY
NAME7 BEAM      10       READY
NAME8 GIRTH     30       NOTREADY
NAME9 GIRTH     25       NOTREADY

TABLE2,

NAME  PROCESS
NAME1 5
NAME2 7
NAME4 10
NAME7 8

So in this flow of software when the item is not ready it will not happened to be present in TABLE2. And table2 is another different process. What I am expecting to see in my output is somthing like this

COMPONENT_RES COMP_READY COMP_NOT_READY TOTAL_PROCESS
COLUMN        40         15             22
BEAM          10         35             10
GIRTH         0          55             0

my query is somethins like this

select distinct md.component COMPONENT_RES, sum(md.quantity) COMP_READY, 
(select sum(md.quantity) COMP_NOT_READY from table1 md where md.status = 'NOTREADY')
from table1 md where md.status = 'ACTIVE'
group by md.component

I have 2 questions here, 1. My query doesnt work in terms on separating the COMP_READY and COMP_NOT_READY 2. I tried million times on how to integrate the TOTAL_PROCESS from the second table when GIRTH is not exists but it has to be shown as 0.

Thanks guys

Upvotes: 0

Views: 54

Answers (2)

Noel
Noel

Reputation: 10525

What you need is a OUTER JOIN between tables.

SQL Fiddle

Query 1:

select 
table1.component_,
sum(case when table1.status_ = 'READY' then table1.quantity else 0 end) comp_ready,
sum(case when table1.status_ = 'NOTREADY' then table1.quantity else 0 end) comp_notready,
sum(coalesce(table2.process_,0)) total_process
from table1 left outer join table2
on table1.name_ = table2.name_
group by table1.component_

Results:

| COMPONENT_ | COMP_READY | COMP_NOTREADY | TOTAL_PROCESS |
|------------|------------|---------------|---------------|
|     COLUMN |         40 |            15 |            22 |
|      GIRTH |          0 |            55 |             0 |
|       BEAM |         10 |            35 |             8 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You need to do two aggregations and join the results together:

select c.component, c.comp_ready, c.comp_notready, cp.total_process
from (select component,
             sum(case when status = 'READY' then Quantity else 0 end) as Comp_Ready,
             sum(case when status = 'NOTREADY' then Quantity else 0 end) as Comp_NotReady,
      from table1
      group by component
     ) c left join
     (select t1.component, sum(t2.component) as total_process
      from table1 t1 join
           table2 t2
           on t1.name = t2.name
      group by t1.component
     ) cp
     on c.component = cp.component;

The trick is that the second subquery needs a join in order to get the appropriate component.

Upvotes: 0

Related Questions