1 Reply Latest reply: Dec 12, 2012 4:13 AM by K_Serge RSS

    Help on VIEW

    894936
      select * from A_TABLE

      AA BB CC DD EE FF GG
      144     051027     31     53     50078685     1     899
      144     051027     31     53     70000087     1     0

      144     051027     14     6     00071163     10     13000
      144     051027     14     6     20058127     2     518
      144     051027     14     6     36284010     2     358
      144     051027     14     6     50030231     1     349
      144     051027     14     6     50036501     10     6990
      144     051027     14     6     50087185     6     1194
      144     051027     14     6     70000087     1     0
      144     051027     14     6     90056945     25     2475
      144     051027     14     6     90087517     20     2980

      140     051027     30     8     20090194     1     4490
      140     051027     30     8     38609700     1     0
      140     051027     30     8     40089401     2     18980
      140     051027     30     8     50090201     1     14000
      140     051027     30     8     70000087     1     0
      140     051027     30     8     80059440     1     2990
      140     051027     30     8     90000072     1     4500

      140     051027     30     9     29630503     3     60
      140     051027     30     9     70000087     1     0
      140     051027     30     9     70021495     6     3000
      140     051027     30     9     90000072     1     500
      140     051027     30     9     90079372     1     299

      140     051027     30     10     20033863     30     164700
      140     051027     30     10     70000087     1     0
      140     051027     30     10     90000072     1     4000

      select * from A_VIEW SHD BE LIKE BELOW WITH additional column A_amount where we need to consider AA,BB,CC,DD if all the four fields are having same value then we need to take sum of GG for
      50078685,     70000087     (aviable in EE fields) ----------------------------------so here i need 899+0 = 899

      for SECOND scenario it shd be like : 1300+518+358+349+6990+1194+0+2475+2980 = 27864 because here we have same AA,BB,CC,DD

      so can you please help me out how to calculate this A_AMOUNT ?

      AA BB CC DD EE FF GG A_AMOUNT
      144     051027     31     53     50078685     1     899 899
      144     051027     31     53     70000087     1     0 899

      144     051027     14     6     00071163     10     13000 27864
      144     051027     14     6     20058127     2     518 27864
      144     051027     14     6     36284010     2     358 27864
      144     051027     14     6     50030231     1     349 27864
      144     051027     14     6     50036501     10     6990 27864
      144     051027     14     6     50087185     6     1194 27864
      144     051027     14     6     70000087     1     0 27864
      144     051027     14     6     90056945     25     2475 27864
      144     051027     14     6     90087517     20     2980 27864

      140     051027     30     8     20090194     1     4490 44960
      140     051027     30     8     38609700     1     0 44960
      140     051027     30     8     40089401     2     18980 44960
      140     051027     30     8     50090201     1     14000 44960
      140     051027     30     8     70000087     1     0 44960
      140     051027     30     8     80059440     1     2990 44960
      140     051027     30     8     90000072     1     4500 44960

      140     051027     30     9     29630503     3     60 3859
      140     051027     30     9     70000087     1     0 3859
      140     051027     30     9     70021495     6     3000 3859
      140     051027     30     9     90000072     1     500 3859
      140     051027     30     9     90079372     1     299 3859

      140     051027     30     10     20033863     30     164700 168700
      140     051027     30     10     70000087     1     0 168700
      140     051027     30     10     90000072     1     4000 168700
      Help me on creating a view  for calculating sum of amounts.
        • 1. Re: Help on VIEW
          K_Serge
          1. This is the forum for Oracle's SQL Developer tool, not for general SQL and PL/SQL questions. Questions like this will get a better response in the PL/SQL forum.

          2. About your VIEW try following :
          create VIEW A_VIEW as
          select <list_of_field>, sum(GG) over (partition by AA,BB,CC,DD) as A_AMOUNT from A_TABLE;

          Edited by: K_Serge on Dec 12, 2012 2:12 AM