RE: SUM totals by relative appearance in output

From: Weiss, Andy <Andy.Weiss_at_oregonstate.edu>
Date: Thu, 30 Jun 2011 16:51:17 -0700
Message-ID: <8715FCA640725A46881FCBF667E43A2C051D94D627_at_EXCH2.nws.oregonstate.edu>

Would this work?

select file_name, min(au_pos), count(*)
  from (select file_name,

               au_pos, 
               au_pos-rank() over (partition by file_name order by au_pos) grp_ind
          from tst2) 

group by grp_ind, filename;

FILE_NAME MIN(AU_POS) COUNT(*)
---------- ----------- ----------

FILE 1               7          1
FILE 1               1          2
FILE 2               3          1
FILE 3               4          3

SQL> desc tst2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(10)
 AU_POS                                             NUMBER

SQL> select * from tst2;
FILE_NAME      AU_POS
---------- ----------
FILE 1              1
FILE 1              2
FILE 2              3
FILE 3              4
FILE 3              5
FILE 3              6
FILE 1              7

• Andy

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave - dherri Sent: Thursday, June 30, 2011 3:11 PM
To: 'oracle-l_at_freelists.org' (oracle-l_at_freelists.org) Subject: SUM totals by relative appearance in output

Folks,

I'm having a bit of a mental block dealing with a query I'm trying to write. What I'd like to do is for data in a table, ordered by some value when queried, to only display totals when rows are repeating by a value, but only doing this totalling by their relative position in the output. (I'm sure there's a better way to say that, but right now even that's beyond my scope.)

Let's say I was trying to map out relative position of files in ASM by their AU position. FILE1 could have 1-to-many consecutive AUs, then another file or free space, then FILE1 could repeat. For example:

FILE NAME	AU POS
---------	------
FILE1 	1
FILE1 	2
FILE2		3
FILE3		4
FILE3		5
FILE3		6
FILE1		7


	...

For the above data I'd want to see:

FILE NAME	STARTING AU POS	LENGTH
---------	---------------	------
FILE1		1			2
FILE2		3			1
FILE3		4			3
FILE1		7			1


	...

I'm convinced I can use an analytic function with the right windowing option to do this all in SQL, but so far I've failed miserably. Obviously I could write PL/SQL to do this, but I've overflown DBMS_OUTPUT's capacity and ended up moving on to capturing output and manipulating it with perl. But that seems like a waste because again, I'm convinced I can do this in SQL without forcing a fork onto something else.

Does any of this make sense and does anyone have a way to do this with SQL?

DAVID HERRING
DBA
Acxiom Corporation

EML   dave.herring_at_acxiom.com
TEL    630.944.4762
MBL   630.430.5988 

1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 30 2011 - 18:51:17 CDT