• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

create a view needing to have 10 different sum() group by queries all having order number in common?

Hi,
What is the best way to create a view needing to have 10 different sum() group by queries all having order number in common?

Was going to use PARTITION BYs
but 2 of the queries have same table and a where statement

select sum(cost_amt) from NOP_Earned_Rev
where TYPE = 'REVENUE'
group by order_num

select sum(cost_amt) from NOP_Earned_Rev
where TYPE = 'UNEARNED'
group by order_num

8 other separate queries with order_num
Thank you,
Bill
0
bcarlis
Asked:
bcarlis
  • 7
  • 6
  • 2
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
select sum(case when type='REVENUE' then cost_amt else 0 end),
sum(case when type='UNEARNED' then cost_amt else 0 end)
from NOP_Earned_Rev
where TYPE in ( 'REVENUE','UNEARNED')
group by order_num
0
 
bcarlisAuthor Commented:
yes.. for those two.. I spaced on the CASE.. duh.. thank you.

But, what about 8 other tables.. 8 different columns.
was thinking subselects.. or unions using all accum. columns - unused as nulls ??

thanks
0
 
slightwv (䄆 Netminder) Commented:
>>But, what about 8 other tables.. 8 different columns.

What 8 other tables?

If you mean 8 other columns just add 3-10 just like the two I added.

If you mean something else, please post the additional information.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
sdstuberCommented:
Can you post your 10 queries and what you would like the output to look like?
0
 
bcarlisAuthor Commented:
8 other separate sum() group by order_num queries
Just making a dashboard with all these totals per order_num

select (select sum(col1) from a where a.order_num = <same as others> group by order_num) col1
          , (select sum(col2) from b where b.order_num = <same as others> group by order_num) col2
          ,  (select sum(col3) from c where c.order_num = <same as others> group by order_num) col3

I can't think..
10 columns.. all have sums from different tables to show for an order number

"order number", col1, col2, col3, col4, col5, col6, col7, col8, col9, col10

i can't pick any of the tables as the one that has all the order numbers.
0
 
slightwv (䄆 Netminder) Commented:
If all the tables involved had an order_num column and you want sums for a provided order_num, just join all the tables and do a regular SUM.

No need for individual inline selects.

However,
We will need to see sample data, tables, expected results, etc... to be able to provide more of a copy/paste solution.
0
 
bcarlisAuthor Commented:
ok, here are the order numbers

select oha.ORDER_NUMBER,  
from oe_order_headers_all oha

thinking
select oha.ORDER_NUMBER, (select sum(colA) from abc where abc.ORDER_NUMBER = oha.ORDER_NUMBER) col1
                       , (select sum(colS) from wbc where wbc.ORDER_NUMBER = oha.ORDER_NUMBER) col2
                       , (select sum(colZ) from aqc where aqc.ORDER_NUMBER = oha.ORDER_NUMBER) col3
                       , etc..
from oe_order_headers_all oha

all but the oe_order_headers_all table are custom tables with FK of order_number
oe_order_headers_all will show all available orders whether any of the custom tables have data or not..

open to better ideas?? does that give enough detail?
0
 
slightwv (䄆 Netminder) Commented:
>>does that give enough detail?

Not really.

I was looking for sample table descriptions, sample data and expected results.

Doesn't have to be 'real' data:
create table abc(ORDER_NUMBER number, col1 number);
insert into abc values(111111,1);
insert into abc values(222222,2);
...

expected results:
order_num     col1   col2   col3
1111111       10      30      60
2222222         0       5       9
...
0
 
awking00Commented:
Is the order_number unique in oe_order_headers_all or are there duplicates?
0
 
bcarlisAuthor Commented:
Yes, the order_number is unique in oe_order_headers_all.
Thank you..
0
 
slightwv (䄆 Netminder) Commented:
Please post sample data and expected results.

It sounds like it is a simple join across all 10 tables and you are trying to make this harder than it needs to be.

I cannot say if it is that simple until I understand the tables and data you are dealing with.

Should go something like:
select ooh.order_num,
     sum(tab1.column),
     sum(tab2.column),
     sum(tab3.column),
...
from oe_order_headers_all ooh
           join tab1 on ooh.order_num=tab1.order_num
           join tab2 on ooh.order_num=tab2.order_num
           join tab3 on ooh.order_num=tab3.order_num
...
0
 
awking00Commented:
I agree with slightwv that it is likely to be simpler than this, but without sample data and expected results, it's hard to say.
select o.order_number,a.col1, b.col2, c.col3,...,j.col10
from oe_order_headers_all o
left join
(select order_number, sum(somecolumn) col1 from sometable <somewhereclause> group by order_number) a
on o.order_number = a.order_number
left join
(select order_number, sum(somecolumn) col2 from sometable <somewhereclause> group by order_number) b
on o.order_number = b.order_number
left join
(select order_number, sum(somecolumn) col3 from sometable <somewhereclause> group by order_number) c
on o.order_number = c.order_number
...
left join
(select order_number, sum(somecolumn) col10 from sometable <somewhereclause> group by order_number) j
on o.order_number = j.order_number
0
 
bcarlisAuthor Commented:
Thank you for your help..
 I am making the create sample tables etc.. I am still designing this that is why I was asking theory level but now I understand much more about my client's needs..
0
 
bcarlisAuthor Commented:
Ok,
results..
ok,
Expected results are like this..
"order number", total_cost1, total_cost2, total_cost3, total_cost4, total_cost5, total_cost6, total_cost7, total_cost8
order 5 will have 5 columns populated, orader 12 - 1 column, etc

I would like to not use oe_order_headers_all because it has millions of rows.
I can't pick any of the custom tables as the one that has all the order numbers.
Some order numbers are varchar because the numbers can be '123-FG'

drop table custom01;
drop table custom02;
drop table custom03;
drop table custom04;
drop table custom05;
drop table custom06;
drop table custom07;
drop table custom08;
drop table oe_order_headers_all;
create table custom01(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom02(ORDER_NUMBER varchar2(100), price number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom03(ORDER_NUMBER varchar2(100), cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom04(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom05(ORDER_NUMBER varchar2(100), cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom06(ORDER_NUMBER varchar2(100), cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom07(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom08(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table oe_order_headers_all (ORDER_NUMBER varchar2(100));
insert into custom01 values(1, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom01 values(1, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom01 values(1, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom01 values(1, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom01 values(5, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom01 values(6, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom01 values(7, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom01 values(8, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom01 values(12, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom01 values(23, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom01 values(33, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom01 values(34, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom02 values('5', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom02 values('5', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom02 values('5', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom02 values('14', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom02 values('5', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom02 values('5', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom02 values('5', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom02 values('5', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom02 values('25', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom02 values('63', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom02 values('17', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom02 values('28', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom03 values('1', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom03 values('1', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom03 values('1', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom03 values('1', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom03 values('5', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom03 values('5', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom03 values('8', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom03 values('8', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom04 values(3, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom04 values(3, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom04 values(3, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom04 values(8, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom04 values(8, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom04 values(8, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom04 values(8, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom04 values(8, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom05 values('12345', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom05 values('3', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom05 values('3', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom05 values('3', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom05 values('5', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom05 values('5', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom05 values('7', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom05 values('7', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom06 values('4', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom06 values('4', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom06 values('4', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom06 values('4', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom06 values('4', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom06 values('4', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom06 values('4', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom06 values('4', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom07 values(1, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom07 values(1, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom07 values(1, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom07 values(5, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom07 values(5, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom07 values(5, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom07 values(7, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom07 values(7, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom08 values(4, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom08 values(4, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom08 values(4, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom08 values(6, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom08 values(6, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom08 values(29, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom08 values(29, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom08 values(29, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
begin
  for j in 1..1000 loop  -- actually this one has millions of rows
  insert into oe_order_headers_all values(j);
  end loop;
end;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>I would like to not use oe_order_headers_all because it has millions of rows.

But order_num should have an index on it so the lookup should be really quick.  Also, millions of rows isn't a lot these days.

>>Expected results are like this..

Based on the data you provided:  What are the 'actual' results?  I was looking for the actual results not a description.


In the original question you had a where clause like:  where TYPE = 'REVENUE'
In the followup post you had:  where wbc.ORDER_NUMBER = oha.ORDER_NUMBER

I still think the regular join that has been proposed will be the best option but we'll need more information before we can provide working SQL.

Once we get that we can work on performance.
0
 
bcarlisAuthor Commented:
Thanks for the help!
I didn't want to create result data..
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now