0

I have temporary table (without primary key) created as a result of few operations which are UNION together and which can be simulated with following one:

  DROP TABLE IF EXISTS temp1;
CREATE TEMP TABLE temp1(rownum int, sname text, input_qty decimal(8,3), 
                        output_qty decimal(8,3), cumulativesum decimal(8,3));

INSERT INTO temp1 (rownum, sname, input_qty, output_qty, cumulativesum) 
VALUES (0,   'name 1',   3.186,     0,      0), 
       (0,   'name 2',   0,      0.24,      0), 
       (0,   'name 3',   0,         1,      0), 
       (0,   'name 4',   0.18,  0.125,      0), 
       (0,   'name 5',   0,      1.14,      0);

During past processes columns 'rownum' and 'cumulativesum' was intentionally filled with zeroes. As a last two steps (or one if possible) I would like to enumerate rownum by +1 from beginning and calculate and write cumulative sum in column 'cumulativesum' to get table ready to write to html document more or less directly.
Cumulative sum should be calculated like:
lastcumulstivesum + input_qty - output_qty.

After all this should be result of operation:

   1,   'name 1'   3.186    0.000      3.186 
   2,   'name 2'   0.000    0.240      2.946 
   3,   'name 3'   0.000    1.000      1.946 
   4,   'name 4'   0.180    0.125      2.001 
   5,   'name 5'   0.000    1.140      0.861

Please if someone can write described query(es) with presented table.

PostgreSQL 9.1, Windows 7

Wine Too
  • 4,515
  • 22
  • 83
  • 137
  • 2
    You need *some* column to define an order over the rows. Otherwise the concept of a "row number" or a "cumulative sum" does not make any sense. If you have such a column you can use a window function to calculate both. See here: http://sqlfiddle.com/#!1/328e9/2 –  Jan 10 '14 at 11:07
  • Hi name, your code gives expected results but if I change names under 'sname' then rows reorders differently. Since those table is already ordered by order of data creaton can this be done without any referent column? So query can do this operation on the fly by keeping order "as is"? For my purpose that would be sensable. – Wine Too Jan 10 '14 at 11:29
  • 2
    The rows in a (relational) table are ***NOT*** "ordered". You can **not** guarantee any order unless you specify an `order by` clause. In your case this means you *have* to include the creation time in the temp table. –  Jan 10 '14 at 11:31
  • 1
    Maybe you can use oid to order the temp. table. Assuming the order in which the rows are inserted is the desired order. – harmic Jan 10 '14 at 11:34
  • 1
    Try this, based on horse's suggestion but using oid to order: http://sqlfiddle.com/#!1/d6263/2 – harmic Jan 10 '14 at 11:42
  • Thanks harmic, that don't work good if I mess names. Maybe would be possible to do ordering by oid's column as reference like this 'over (order by oid_column)'? If oid have his column? – Wine Too Jan 10 '14 at 11:51
  • Yes, that seem's to work! 'select row_number() over (order by oid) as rownum' when table is created with oids. – Wine Too Jan 10 '14 at 11:53

0 Answers0