Home » SQL & PL/SQL » SQL & PL/SQL » SUM of records (oracle 10g 10.0.2.0)
SUM of records [message #360593] |
Fri, 21 November 2008 09:07  |
kumarprabhash
Messages: 2 Registered: November 2008
|
Junior Member |
|
|
ITEM TYPE1 TYPE2 TYPE3 TYPE4 COST
-------------------------------------------------------------
P1 1 1 1 T1 100
P2 2 2 2 T2 100
P3 3 3 3 T3 100
P1 4 4 4 T1 100
P1 5 5 5 T1 100
P1 6 6 6 T1 100
P4 7 7 7 T4 100
P5 8 8 8 T5 100
-------------------------------------------------------------
ACTUALLY, IN THE ABOVE DATASET, WHEREVER [ITEM-TYPE4] IS SAME,
RECORDS ARE GOING TO BE SUMMED UP COSTWISE. SO HERE P1-T1 IS SAME
FOR 4 RECORDS, SO ALL FOUR RECORDS ARE SUMMED UP AND SHOWN WITH THE
REST OF THE RECORDS. HERE COLUMN VALUES FOR TYPE1,TYPE2,TYPE3 WILL BE
SELECTED FROM ANY ONE OF THEM. IN THE BELOW ONE, FIRST RECORD'S
TYPE1,TYPE2,TYPE3 VALUES HAS BEEN SELECTED.
HOW TO ARRIVE AT THIS RESULT SET USING SINGLE QUERY?
ITEM TYPE1 TYPE2 TYPE3 TYPE4 COST
-------------------------------------------------------------
P1 1 1 1 T1 400
P2 2 2 2 T2 100
P3 3 3 3 T3 100
P4 7 7 7 T4 100
P5 8 8 8 T5 100
-------------------------------------------------------------
THANKS IN ADVANCE
PK
|
|
|
Re: SUM of records [message #360597 is a reply to message #360593] |
Fri, 21 November 2008 09:40   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Something like this should do the jobcreate table tesT_026 (item varchar2(10), type1 number, type2 number ,type3 number, type4 varchar2(10), cost number);
insert into test_026 values ('P1' ,1 ,1 ,1 ,'T1', 100);
insert into test_026 values ('P2' ,2 ,2 ,2 ,'T2', 100);
insert into test_026 values ('P3' ,3 ,3 ,3 ,'T3', 100);
insert into test_026 values ('P1' ,4 ,4 ,4 ,'T1', 100);
insert into test_026 values ('P1' ,5 ,5 ,5 ,'T1', 100);
insert into test_026 values ('P1' ,6 ,6 ,6 ,'T1', 100);
insert into test_026 values ('P4' ,7 ,7 ,7 ,'T4', 100);
insert into test_026 values ('P5' ,8 ,8 ,8 ,'T5', 100);
SELECT ITEM
,min(type1) KEEP (DENSE_RANK FIRST ORDER BY rowid) type1
,min(type2) KEEP (DENSE_RANK FIRST ORDER BY rowid) type2
,min(type3) KEEP (DENSE_RANK FIRST ORDER BY rowid) type3
,type4
,sum(cost)
FROM test_026
GROUP BY item,type4;
You can read about the KEEP (DENSE_RANK ... syntax here
|
|
|
Re: SUM of records [message #360620 is a reply to message #360597] |
Fri, 21 November 2008 11:55   |
kumarprabhash
Messages: 2 Registered: November 2008
|
Junior Member |
|
|
Hi JRowbottom,
Thanks a lot for your reply. One more query I have is - if value of type1, type2, type3 is of varchar2 type and not of number as i had given, then will the query of yours work?
Please let me know your thoughts.
eagerly awaiting your reply.
best rgds,
pk
[Updated on: Fri, 21 November 2008 11:59] by Moderator Report message to a moderator
|
|
|
Re: SUM of records [message #360622 is a reply to message #360620] |
Fri, 21 November 2008 12:00  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | if value of type1, type2, type3 is of varchar2 type and not of number as i had given, then will the query of yours work?
|
What about trying it by yourself?
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Feb 18 01:49:51 CST 2025
|