OraFAQ Forum: Reports & Discoverer » get total of records in different row-number

Home » Developer & Programmer » Reports & Discoverer » get total of records in different row-number

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

get total of records in different row-number [message #389306]

Sat, 28 February 2009 05:45

amdabd
Messages: 91
Registered: November 2007
Location: My Computer

Member

hi all
how could I get totals of records in different row-number?
/forum/fa/5814/0/

as in image
cell C2 = A2 "same record" + B3 " next record"
at last record of "A column 'val_a' "
'val_total' will be " null " as no entry in next record row-number of " B column 'val_b' ".

thanks
regards

Attachment: haz_rep.JPG
(Size: 20.71KB, Downloaded 1308 times)

Report message to a moderator

Re: get total of records in different row-number [message #389320 is a reply to message #389306]

Sat, 28 February 2009 09:10

amdabd
Messages: 91
Registered: November 2007
Location: My Computer

Member

hi
I got it,
but still one problem
--
I use a view

CREATE VIEW v_amd (r1, vala, r2, valb)
AS
   SELECT ROWNUM, vala, ROWNUM, valb
     FROM amd

then I use the query

SELECT c.vala, c.valb, a.vala + b.valb
  FROM (SELECT r1, vala
          FROM v_amd) a,
       (SELECT r2, valb
          FROM v_amd) b,
       (SELECT vala, valb
          FROM amd) c
 WHERE b.r2 = a.r1 + 1 AND a.vala = c.vala

the output was
/forum/fa/5820/0/

that is what partially I need, however the output should be

VALA         VALB    val_total
---------- ---------- ----------
   1          1          3
   2          2          5
   3          3          7
   4          4          9
   5          5         11
   6          6

I think my problem within the " Where Clause " in the Query but cannot get it
thanks

Attachment: 2.JPG
(Size: 4.06KB, Downloaded 1405 times)

Report message to a moderator

Re: get total of records in different row-number [message #389323 is a reply to message #389320]

Sat, 28 February 2009 11:25

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's a certain discrepancy between Excel screenshot in the first message and desired output in the second message.

First                    Second

val_a  val_b  total      val_a  val_b  total
-----  -----  -----      -----  -----  -----
1      0      2          1      1      3
2      1      4          2      2      5
3      2      6          3      3      7
4      3      8          4      4      9
5      4                 5      5      11
                         6      6

So, which one would you like to have?

Anyway: perhaps you should take a look at the LEAD Oracle function. Here's an example which deals with the first sample data:

SQL> SELECT * FROM test;

     VAL_A      VAL_B
---------- ----------
         1          0
         2          1
         3          2
         4          3
         5          4

SQL> SELECT val_a, val_b,
  2    val_a + lead(val_b) over (ORDER BY val_a) total
  3  FROM TEST;

     VAL_A      VAL_B      TOTAL
---------- ---------- ----------
         1          0          2
         2          1          4
         3          2          6
         4          3          8
         5          4

SQL>

Report message to a moderator

Re: get total of records in different row-number [message #389337 is a reply to message #389306]

Sat, 28 February 2009 16:38

amdabd
Messages: 91
Registered: November 2007
Location: My Computer

Member

As usual thanks Littlefoot
and as usual it works Smile

for my previous two messages there are not any discrepancy between Excel screenshot and desired output.
I try to give an example in Excel screenshot.

thanks again Smile

regards

Report message to a moderator

Previous Topic:	How can I add these sentences
Next Topic:	Labels Rotation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 23 14:27:39 CDT 2024