Home » SQL & PL/SQL » SQL & PL/SQL » Need to take different column & row values into a single row (merged)
Need to take different column & row values into a single row (merged) [message #410720] Mon, 29 June 2009 22:59 Go to next message
stuti.raj
Messages: 6
Registered: March 2009
Location: India
Junior Member
I have data in the following format:

Processed  Succeeded  Errored	Start Time	       End Time
107	   77	      30	6/15/2009 2:50:30 PM   6/15/2009 3:06:22 PM
117	   117	      0	        6/15/2009 3:06:22 PM   6/15/2009 6:02:54 PM
108	   108	      0	        6/15/2009 6:02:54 PM   6/15/2009 6:35:44 PM
218	   217	      1	        6/15/2009 6:35:44 PM   6/15/2009 7:10:13 PM
324	   324	      0	        6/15/2009 7:10:13 PM   6/15/2009 7:31:49 PM
1512	   1512	      0	        6/15/2009 7:31:49 PM   6/15/2009 7:47:02 PM
648	   648	      0	        6/15/2009 7:47:02 PM   6/15/2009 8:11:57 PM
1078	   1078	      0	        6/15/2009 8:11:57 PM   6/15/2009 10:32:44 PM
95	   25	      70	6/15/2009 10:32:44 PM  6/16/2009 12:03:06 PM


Now the expected output what i want is:

Processed   Succeeded   Errored	  Start Time            End Time
4207        4106        101       6/15/2009 2:50:30 PM  6/16/2009 12:03:06 PM



So just wanted to know that is there any way to take different start & end time values into a single row with the sum of processed, succeeded & errored values.

Thanks
Re: Need to take different column & row values into a single row (merged) [message #410723 is a reply to message #410720] Mon, 29 June 2009 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem, the appropriate aggregate function for each column: SUM, MIN, MAX.

Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Mon, 29 June 2009 23:25]

Report message to a moderator

Re: Need to take different column & row values into a single row (merged) [message #410725 is a reply to message #410723] Mon, 29 June 2009 23:35 Go to previous messageGo to next message
stuti.raj
Messages: 6
Registered: March 2009
Location: India
Junior Member
Hi,

Thanks a lot, got what i want Smile

select sum(pw.processed) "Processed", 
sum(pw.succeeded) "Succeeded", 
sum(pw.errored) "Errored", 
min(pw.start_time) "Start Time", 
max(pw.end_time) "End Time"
from process_work pw
where pw.process_id=755403
and substr(to_char(pw.START_TIME,'DD-Mon-YY HH:MI:SS'),1,9)  =  '15-Jun-09'


Processed   Succeeded   Errored	  Start Time            End Time
4207        4106        101       6/15/2009 2:50:30 PM  6/16/2009 12:03:06 PM

[Updated on: Tue, 30 June 2009 00:21] by Moderator

Report message to a moderator

Re: Need to take different column & row values into a single row (merged) [message #410729 is a reply to message #410725] Tue, 30 June 2009 00:26 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not
substr(to_char(pw.START_TIME,'DD-Mon-YY HH:MI:SS'),1,9)  =  '15-Jun-09'

but
trunc(pw.START_TIME) = to_date('15/06/2009','DD/MM/YYYY')

or better (if you have an index on pw.START_TIME)
    pw.START_TIME >= to_date('15/06/2009','DD/MM/YYYY') 
and pw.START_TIME < to_date('15/06/2009','DD/MM/YYYY')+1

Regards
Michel
Previous Topic: Procedure execution hierarchy
Next Topic: Update Query Help require
Goto Forum:
  


Current Time: Thu Dec 08 20:00:29 CST 2016

Total time taken to generate the page: 0.16410 seconds