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 Go to next message
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something like this should do the job
create 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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: After Trigger is not working .
Next Topic: ORA-01406: fetched column value was truncated
Goto Forum:
  


Current Time: Fri Dec 02 14:22:13 CST 2016

Total time taken to generate the page: 0.12300 seconds