We help IT Professionals succeed at work.

# Need Help With SQL Query for Oracle RDB (Part 2)

on
390 Views
This is a follow up to a previous question (search on the question title).

Table 1: POWERON
HEATNUMBER   BUCKETSEQUENCE   CONSUMPTION
981011                      1                                   100
981011                      1                                   200
981011                      1                                   300
981011                      2                                   400
981011                      3                                   500
981011                      3                                   600

Table 2: BUCKETS_HEAT
HEATNUMBER   BUCKETSEQUENCE   MATWEIGHTACT1   MATWEIGHTACT2
981011                      1                                          10000           20000
981011                      2                                          30000           40000
981011                      3                                          50000           60000

My goal was to get total CONSUMPTION and TOTAL_METALLIC_WEIGHT (MATWEIGHTACT1 + MATWEIGHTACT2) for each BUCKETSEQUENCE.

Here is the query that works:

SELECT b.heatnumber as HEAT_NUMBER,
b.bucketsequence as BUCKET_SEQUENCE,
sum(b.matweightact1  + b.matweightact2 ) as TOTAL_METALLIC_WEIGHT,
sum(p.consumption) as POWER_CONSUMED
from buckets_heat b,
(select heatnumber, bucketsequence, sum(consumption) consumption  from poweron  group by heatnumber, bucketsequence ) p where b.heatnumber = '981011'
and b.heatnumber = p.heatnumber
and b.bucketsequence = p.bucketsequence group by b.heatnumber,
b.bucketsequence,
p.heatnumber,
p.bucketsequence
order by b.heatnumber,
b.bucketsequence;

This is the result:
HEAT_NUMBER   BUCKET_SEQUENCE   TOTAL_METALLIC_WEIGHT         POWER_CONSUMED
981011                      1                                                      30000                  600
981011                      2                                                      70000                  400
981011                      3                                                     110000                 1100

This solution worked perfectly.  Now the problem:

A new constraint is to only include a material of Type 1 from this table...

Table 3: TABMAT
MATCODE   TYPE
123              1
456              2

Table BUCKETS_HEAT has MATCODE1 and MATCODE2 fields corresponding to the weight above.

Table 2: BUCKETS_HEAT
HEATNUMBER   BUCKETSEQUENCE   MATWEIGHTACT1   MATCODE1    MATWEIGHTACT2     MATCODE2
981011                      1                                          10000    123                 20000                        456
981011                      2                                          30000    456                 40000                        123
981011                      3                                          50000    123                 60000                        456

So the new result should look like this:
HEAT_NUMBER   BUCKET_SEQUENCE   TOTAL_METALLIC_WEIGHT         POWER_CONSUMED
981011                      1                                                      10000                  600
981011                      2                                                      40000                  400
981011                      3                                                      50000                 1100

where the weight from material 456 is excluded from the total.

Comment
Watch Question

## View Solution Only

Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
Data Architect
CERTIFIED EXPERT

Commented:
Try this query:

``````SELECT b.heatnumber as HEAT_NUMBER,
b.bucketsequence as BUCKET_SEQUENCE,
sum(b.wt ) as TOTAL_METALLIC_WEIGHT,
sum(p.consumption) as POWER_CONSUMED
from
(select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT1 wt,  MATCODE1 matcode from buckets_heat
union all
select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT2 wt,  MATCODE2 matcode from buckets_heat )b,
(select heatnumber, bucketsequence, sum(consumption) consumption  from poweron  group by heatnumber, bucketsequence ) p ,
tabmat t
where b.heatnumber = '981011'
and b.heatnumber = p.heatnumber
and b.bucketsequence = p.bucketsequence
and t.type = 1
and b.matcode = t.matcode
group by b.heatnumber,
b.bucketsequence
order by b.heatnumber,
b.bucketsequence;
``````

Commented:
sujith80,

Your query works except the consumption value is 2X the correct value.

Commented:
rbrooker,

Your query works except the total_metallic_weight = 0.  The power_consumed value ois correct.

Commented:
rbrooker,

I think I found the error/typo...

line 8: b.matcode should be b.type
line 10: c.matcode should be c.type
I am testing this now...

Top Expert 2006

Commented:
yep, your right...  perils of suppliying untested code...
Data Architect
CERTIFIED EXPERT

Commented:
>> query works except the consumption value is 2X the correct value.
This will happen only if you maintain the same value for matcode1 and matcode2.
Try the below which handles this.

``````SQL> select * from POWERON ;

HEATNUMBER BUCKETSEQUENCE CONSUMPTION
---------- -------------- -----------
981011              1         100
981011              1         200
981011              1         300
981011              2         400
981011              3         500
981011              3         600

6 rows selected.

SQL> select * from BUCKETS_HEAT ;

HEATNUMBER BUCKETSEQUENCE MATWEIGHTACT1   MATCODE1 MATWEIGHTACT2   MATCODE2
---------- -------------- ------------- ---------- ------------- ----------
981011              1         10000        123         20000        456
981011              2         30000        456         40000        123
981011              3         50000        123         60000        456

3 rows selected.

SQL> select * from TABMAT;

MATCODE       TYPE
---------- ----------
123          1
456          2

2 rows selected.

SQL> SELECT b.heatnumber as HEAT_NUMBER,
2  b.bucketsequence as BUCKET_SEQUENCE,
3  sum(b.wt ) as TOTAL_METALLIC_WEIGHT,
4  sum(p.consumption) as POWER_CONSUMED
5  from
6  (select HEATNUMBER, BUCKETSEQUENCE,  sum(wt) wt, matcode
7  from (
8  select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT1 wt,  MATCODE1 matcode from buckets_heat
9   union all
10   select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT2 wt,  MATCODE2 matcode from buckets_heat)
11  group by HEATNUMBER, BUCKETSEQUENCE, matcode )b,
12  (select heatnumber, bucketsequence, sum(consumption) consumption  from poweron  group by heatnumber, bucketsequence ) p ,
13  tabmat t
14  where b.heatnumber = '981011'
15    and b.heatnumber = p.heatnumber
16    and b.bucketsequence = p.bucketsequence
17    and t.type = 1
18    and b.matcode = t.matcode
19  group by b.heatnumber,
20           b.bucketsequence
21  order by b.heatnumber,
22           b.bucketsequence;

HEAT_NUMBER BUCKET_SEQUENCE TOTAL_METALLIC_WEIGHT POWER_CONSUMED
----------- --------------- --------------------- --------------
981011               1                 10000            600
981011               2                 40000            400
981011               3                 50000           1100

3 rows selected.

SQL>
``````

Commented:
Thanks again!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
###### Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

###### Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

• Troubleshooting
• Research
• Professional Opinions
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.