Sum of Data from Multiple Columns 2

[cwsstins]

I've got an app with a Supply table and a Detail table. Detail table/screen has 6 different "Item" and "Quantity" fields, so you can order up to 6 items. Each item field can select any item from the Supply table.

Supply.idno -> Detail.item1
...
Supply.idno -> Detail.item6

So from any Detail record, the Supply item "AAAAA" could show up in Detail.item1, Detail.item2, ..., Detail.item6.

I'm trying to run either a query or Crystal report that will group by Supply.idno and give me a sum of quantity of each item no matter which field it shows up in.

I came up with the following query to get the sum for just one Detail field, but can't figure out how to aggregate them all. I would rather not have to run this query 6 times and manually add up all the quantities:

select s.idno, sum(d.qty_1) from supply s, detail d where s.idno = d.itemcode_1 group by s.idno;

 

[SantaMufasa]

stinsman,

<soapbox>

You are aware, I hope, that your data badly disobeys First Normal Form. It is a bad design, which contributes to the programmatical challenge you face now. I'm guessing that your response will be, "Yeah, I know it's bad, but it's legacy data that I'm not allowed to change."

At some point (why not now?), someone should take ownership of the quality of data and make you application work with the correct design.

</soapbox>

Now, to deal with your current need: Can you please confirm what/how you want your data values summed? Please post a few rows of sample data and the resulting output you want from those rows.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Actually, I do have the capability to make changes to this system...how would you suggest that this type of functionality work?

As to the current need...here is some sample data as you requested:

Supply.idno
AAAAA
BBBBB
CCCCC

Detail
RECORD 1
item1 = CCCCC qty = 50
item2 = AAAAA qty = 10
...
item6 = BBBBB qty = 25

RECORD 2
item1 = BBBBB qty = 30
item2 = CCCCC qty = 15
...
item6 = AAAAA qty = 90

So I'm looking for a query that would produce the results:
Item Sum
AAAAA - 100
BBBBB - 55
CCCCC - 65

 

[SantaMufasa]

Here, then, are sample data from your previous post (expanded across all six slots):

select * from detail;

ITEM1 QTY1 ITEM2 QTY2 ITEM3 QTY3 ITEM4 QTY4 ITEM5 QTY5 ITEM6 QTY6
----- ---- ----- ---- ----- ---- ----- ---- ----- ---- ----- ----
CCCCC   50 AAAAA   10                                  BBBBB   25
                      BBBBB   30 CCCCC   15 AAAAA   90
*****************************************************************

Here is code that produces the results you want:

select item, sum(qty) sum
from
(select item1 item,qty1 qty from detail
union
select item2,qty2 from detail
union
select item3,qty3 from detail
union
select item4,qty4 from detail
union
select item5,qty5 from detail
union
select item6,qty6 from detail)
group by item;

ITEM         SUM
----- ----------
AAAAA        100
BBBBB         55
CCCCC         65

Actually, I do have the capability to make changes to this system...how would you suggest that this type of functionality work?

I recommend that you create a "LINE_ITEM" table with the results of the multiple "UNION" actions, above:

create table line_item as
(select item1 item,qty1 qty from detail
union
select item2,qty2 from detail
union
select item3,qty3 from detail
union
select item4,qty4 from detail
union
select item5,qty5 from detail
union
select item6,qty6 from detail);

SQL> desc line_item
 Name                    Null?    Type
 ----------------------- -------- -----------
 ITEM                             VARCHAR2(5)
 QTY                              NUMBER

SQL> select * from line_item;

ITEM         QTY
----- ----------
AAAAA         10
AAAAA         90
BBBBB         25
BBBBB         30
CCCCC         15
CCCCC         50

Once you have the LINE_ITEM table, your data is properly normalised.


Let us know if all this satisfies your need or if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

I see...so basically, create a new table that looks at the existing table and use that for reporting purposes. But to the user, the existing Detail table will not change, they will still see the same functionality...correct?

As for the statement itself...I seem to be getting conflicting results. The results from the union query brings back less than the sum of manually adding up all the individual queries.

For instance:
UNION query:
Item Sum
AAAAA 26

Individual queries:
Item1 Qty1 Item4 Qty4
AAAAA 20 AAAAA 13

 

[carp]

If you cannot change the table structure (due, for instance, to many applications that depend on the current structure), you might want to consider a view for your reporting purposes. You can do this by leveraging SantaMufasa's excellent example:

CREATE OR REPLACE VIEW normalized_data_view AS
select item1 item,qty1 qty from detail
union
select item2,qty2 from detail
union
select item3,qty3 from detail
union
select item4,qty4 from detail
union
select item5,qty5 from detail
union
select item6,qty6 from detail;

Then you could query the view with more straightforward queries:

SELECT item, sum(quantity) 
  FROM normalized_item_view 
GROUP BY item;

Once you have the view in place, if you were so inclined you could start modifying existing code to pull data from the view instead of the table. Once all of the applications are modified, you could create a permanent table with the above structure, populate the table, drop the view, and rename the table with the name of the view. Or - leave things as they are and live with the view.

 

[SantaMufasa]

As usual, Carp's suggestions are excellent! I highly recommend his "phased implementation" method as the one to follow to correct your "First Normal Form" problems.

And your numeric error is most likely due to our using "UNION" instead of the more logically correct "UNION ALL" in your case. The difference is that "UNION" combines, into a single row, any duplicate rows of data. In your case, we need any duplicate rows to produce correct results. So, simply change any references to "UNION" in above code to "UNION ALL".

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Awesome guys...the union all worked. I will look at what it will take to put in the normalization in test.

Thanks much!

 

[Beilstwh]

By the way, he is NOT saying to create a temporary table for reporting purposes, he is saying to replace your detail table that can hold a maximum of 6 items, into a table like the following

idno varchar2(5);
qty number;


You could have 100 rows for BBBBB or 1 and your structure would handle it. and to get the sum, all you would do is

select s.idno,sum(b.qty)
from detail b, supply s
where s.idno = b.idno;








Bill
Oracle DBA/Developer
New York State, USA

 

[Beilstwh]

Sorry, I hit submit to quick, the query would be

select s.idno,sum(b.qty)
from detail b, supply s
where s.idno = b.idno
group by s.idno;

Bill
Oracle DBA/Developer
New York State, USA

 

[cwsstins]

In the application, the detail table (screen) is related to another table that is the primary table for logging the record. The detail screen has the 6 lines so that a user can input up to 6 orders on one record. If I used the example above, the user would have to create 6 new records to put in 6 orders and that's not acceptable (or time-effective) for their use of the application.

 

[Beilstwh]

Not true, you handle the multiple rows behind the scene in the form.

Bill
Oracle DBA/Developer
New York State, USA