Home » SQL & PL/SQL » SQL & PL/SQL » summing up values...
summing up values... [message #260261] Fri, 17 August 2007 16:48 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
I need help in aggregating the item_weight based on in_id.



tree_id in_id  item_wgt

SP1000	01	(sum of the weights of 011+ 013+ ...)
SP1000	011     (sum of the weights of 0111 + 0113 + ...)	
SP1000	0111	(sum of 804075 + 2331442 + 219495)
SP1000	011101	804075
SP1000	011102	2331442
SP1000	011103	219495
SP1000	0113	(sum of 142278 + 4526592+ 30695 + 0 + 1392460)
SP1000	011301	142278
SP1000	011302	4526592
SP1000	011303	30695
SP1000	011304	0
SP1000	011305	1392460
SP1000	0119	
SP1000	011901	2164794
SP1000	012	
SP1000	0121	
SP1000	012101	7840664
SP1000	0122	
SP1000	012201	800034
SP1000	012202	30279542
SP1000	012203	140216
SP1000	012204	15883
SP1000	012205	1293263
SP1000	0123	
SP1000	012301	1226926
SP1000	013	



Anu
Re: summing up values... [message #260264 is a reply to message #260261] Fri, 17 August 2007 16:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try to search google.

By
Vamsi
Re: summing up values... [message #260283 is a reply to message #260261] Sat, 18 August 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use ROLLUP function.

If you have posted a test case (create table + insert statements), I'd show how to do it.

Regards
Michel
Re: summing up values... [message #260626 is a reply to message #260283] Mon, 20 August 2007 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't think of a way to use Rollup (but I've never really used it in anger, so I look forwards to finding out how to do it)

You can do it with a row generator technique, like this:
create table rollup_test (col_1  varchar2(10), amt number);

insert into rollup_test values ('01'	,'');
insert into rollup_test values ('011'   ,'');
insert into rollup_test values ('0111'	,'');
insert into rollup_test values ('011101',	804075);
insert into rollup_test values ('011102',	2331442);
insert into rollup_test values ('011103',	219495);
insert into rollup_test values ('0113'	,'');
insert into rollup_test values ('011301',	142278);
insert into rollup_test values ('011302',	4526592);
insert into rollup_test values ('011303',	30695);
insert into rollup_test values ('011304',	0);
insert into rollup_test values ('011305',	1392460);
insert into rollup_test values ('0119'	,'');
insert into rollup_test values ('011901',	2164794);
insert into rollup_test values ('012'	,'');
insert into rollup_test values ('0121'	,'');
insert into rollup_test values ('012101',	7840664);
insert into rollup_test values ('0122'	,'');
insert into rollup_test values ('012201',	800034);
insert into rollup_test values ('012202',	30279542);
insert into rollup_test values ('012203',	140216);
insert into rollup_test values ('012204',	15883);
insert into rollup_test values ('012205',	1293263);
insert into rollup_test values ('0123'	,'');
insert into rollup_test values ('012301',	1226926);
insert into rollup_test values ('013'	,'');

select col_1,sum(amt)
from (
select substr(col_1,1,len.l) col_1, nvl(amt,0) amt
from   rollup_test
      ,(select level l from dual connect by level <=6) len
where  l in (2,3,4,6))
group by col_1
order by col_1;

COL_1	SUM(AMT)
01	    53208359
011	    11611831
0111	3355012
011101	804075
011102	2331442
011103	219495
0113	6092025
011301	142278
011302	4526592
011303	30695
011304	0
011305	1392460
0119	2164794
011901	2164794
012	    41596528
0121	7840664
012101	7840664
0122	32528938
012201	800034
012202	30279542
012203	140216
012204	15883
012205	1293263
0123	1226926
012301	1226926
013	    0
Re: summing up values... [message #260671 is a reply to message #260626] Mon, 20 August 2007 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's easier to show when you have a test case. Wink
SQL> select * from rollup_test order by 1;
COL_1             AMT
---------- ----------
011101         804075
011102        2331442
011103         219495
011301         142278
011302        4526592
011303          30695
011304              0
011305        1392460
011901        2164794
012101        7840664
012201         800034
012202       30279542
012203         140216
012204          15883
012205        1293263
012301        1226926

16 rows selected.

SQL> select decode(grouping_id(substr(col_1,1,2),substr(col_1,1,3),substr(col_1,1,4),col_1),
  2                0, '', 'Sum-->') col_0,
  3         decode(grouping_id(substr(col_1,1,2),substr(col_1,1,3),substr(col_1,1,4),col_1),
  4                0, col_1,
  5                1, substr(col_1,1,4),
  6                3, substr(col_1,1,3),
  7                7, substr(col_1,1,2)) col_1,
  8         sum(amt) amt
  9  from rollup_test
 10  group by rollup (substr(col_1,1,2),substr(col_1,1,3),substr(col_1,1,4),col_1)
 11  having grouping_id(substr(col_1,1,2),substr(col_1,1,3),substr(col_1,1,4),col_1) != 15
 12  /
COL_0  COL_1             AMT
------ ---------- ----------
       011101         804075
       011102        2331442
       011103         219495
Sum--> 0111          3355012
       011301         142278
       011302        4526592
       011303          30695
       011304              0
       011305        1392460
Sum--> 0113          6092025
       011901        2164794
Sum--> 0119          2164794
Sum--> 011          11611831
       012101        7840664
Sum--> 0121          7840664
       012201         800034
       012202       30279542
       012203         140216
       012204          15883
       012205        1293263
Sum--> 0122         32528938
       012301        1226926
Sum--> 0123          1226926
Sum--> 012          41596528
Sum--> 01           53208359

25 rows selected.

Regards
Michel
Re: summing up values... [message #260892 is a reply to message #260671] Tue, 21 August 2007 03:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very nice.
Thanky you for that example.
Re: summing up values... [message #260909 is a reply to message #260892] Tue, 21 August 2007 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for providing the test case. Wink

Regards
Michel
Re: summing up values... [message #261027 is a reply to message #260261] Tue, 21 August 2007 09:41 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Thanks to both of you...

Both solutions worked.

I apologize for not including the test case. From next time, i would include one for ease of use for everyone.

Anu
Re: summing up values... [message #298539 is a reply to message #261027] Wed, 06 February 2008 05:34 Go to previous message
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
Junior Member
that's a real good example - I'm going to keep that....
Previous Topic: External table
Next Topic: find recurring balance
Goto Forum:
  


Current Time: Sat Dec 03 22:23:17 CST 2016

Total time taken to generate the page: 0.14701 seconds