Home » Developer & Programmer » Reports & Discoverer » get total of records in different row-number
get total of records in different row-number [message #389306] Sat, 28 February 2009 05:45 Go to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi all
how could I get totals of records in different row-number?
./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 310 times)
Re: get total of records in different row-number [message #389320 is a reply to message #389306] Sat, 28 February 2009 09:10 Go to previous messageGo to next message
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
./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 297 times)
Re: get total of records in different row-number [message #389323 is a reply to message #389320] Sat, 28 February 2009 11:25 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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>
Re: get total of records in different row-number [message #389337 is a reply to message #389306] Sat, 28 February 2009 16:38 Go to previous message
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
Previous Topic: How can I add these sentences
Next Topic: Labels Rotation
Goto Forum:
  


Current Time: Wed Dec 07 10:43:44 CST 2016

Total time taken to generate the page: 0.27341 seconds