Sum the “previous row” value with existing row value in a SELECT statement

Question

I want to sum the previous value with the existing value in that row.

Here's my code:

SELECT
    co.partner_id,
    to_char(co.date, 'DD') AS day,
    to_char(co.date, 'MM') AS month,
    to_char(co.date, 'YYYY') AS year,
    sum(col.qty * p.price) AS priceday
FROM
    order_detail col
    JOIN
ORDER co ON co.id = col.order_id
JOIN product p ON p.id = col.product_id
GROUP BY
    co.partner_id,
    to_char(co.date, 'MM'),
    to_char(co.date, 'YYYY'),
    to_char(co.date, 'DD')

that code become like this:

the table sould be like this :

Thank You.

Answer 1

You can use Window Functions with Frame clause.

If you want to SUM with the previous row then you will do:

SELECT o.partner_id, o.date, SUM(SUM(p.price * od.qty)) OVER (PARTITION BY o.partner_id ORDER BY o.partner_id, o.date ROWS 1 PRECEDING) AS priceday
    FROM test.order AS o
    INNER JOIN test.order_detail AS od
      ON o.id = od.order_id
    INNER JOIN test.product AS p
      ON od.product_id = p.id
    GROUP BY o.partner_id, o.date;

Notice the ROWS 1 PRECEDING.

If you want to SUM with all the previous rows (running total) then you will do:

SELECT o.partner_id, o.date, SUM(SUM(p.price * od.qty)) OVER (PARTITION BY o.partner_id ORDER BY o.partner_id, o.date ROWS UNBOUNDED PRECEDING) AS priceday
    FROM test.order AS o
    INNER JOIN test.order_detail AS od
      ON o.id = od.order_id
    INNER JOIN test.product AS p
      ON od.product_id = p.id
    GROUP BY o.partner_id, o.date;

Notice the ROWS UNBOUNDED PRECEDING.

Explanation

SUM(SUM(p.price * od.qty)) OVER (PARTITION BY o.partner_id ORDER BY o.partner_id, o.date ROWS 1 PRECEDING) AS priceday is the main actor:

• SUM(p.price * od.qty) - computes the price per day
• SUM(SUM(...)) OVER (...) - sums multiple prices on multiple days
• PARTITION BY o.partner_id - required in order to keep the SUM within the boundaries of the partner_id
• ORDER BY o.partner_id, o.date - required to order the rows within the partition by date
• ROWS 1 PRECEDING - in order to include the previous row in the SUM along with the current row

Complete example (for easier testing)

CREATE SCHEMA test;

CREATE TABLE test.order (
  id SERIAL PRIMARY KEY,
  partner_id int,
  date date
);

CREATE TABLE test.product (
  id SERIAL PRIMARY KEY,
  price DECIMAL
);

CREATE TABLE test.order_detail (
  id SERIAL PRIMARY KEY,
  order_id int REFERENCES test.order (id),
  product_id int REFERENCES test.product (id),
  qty int
);

INSERT INTO test.order
  (partner_id, date)
  VALUES
    (531, '2017-06-20'),
    (531, '2017-06-21'),
    (531, '2017-06-22'),
    (532, '2017-06-20'),
    (532, '2017-06-20'),
    (532, '2017-06-22'),
    (532, '2017-06-23');

INSERT INTO test.product
  (price)
  VALUES
    (1000.0);

INSERT INTO test.order_detail
  (order_id, product_id, qty)
  VALUES
    (1, 1, 300),
    (2, 1, 230),
    (3, 1, 130),
    (4, 1, 300),
    (5, 1, 230),
    (6, 1, 130),
    (7, 1, 100);

-- sum with the previous row
SELECT o.partner_id, o.date, SUM(SUM(p.price * od.qty)) OVER (PARTITION BY o.partner_id ORDER BY o.partner_id, o.date ROWS 1 PRECEDING) AS priceday
    FROM test.order AS o
    INNER JOIN test.order_detail AS od
      ON o.id = od.order_id
    INNER JOIN test.product AS p
      ON od.product_id = p.id
    GROUP BY o.partner_id, o.date;

-- sum with all the previous rows
SELECT o.partner_id, o.date, SUM(SUM(p.price * od.qty)) OVER (PARTITION BY o.partner_id ORDER BY o.partner_id, o.date ROWS UNBOUNDED PRECEDING) AS priceday
    FROM test.order AS o
    INNER JOIN test.order_detail AS od
      ON o.id = od.order_id
    INNER JOIN test.product AS p
      ON od.product_id = p.id
    GROUP BY o.partner_id, o.date;

DROP SCHEMA test CASCADE;

Answer 2

Here is an example of what you need (I hope):

test=# with nums(n) as (
  select z from generate_series(1, 10) as _(z)
)
select
  n,
  sum(n) over (order by n)
from nums;
 n  | sum
----+-----
  1 |   1
  2 |   3
  3 |   6
  4 |  10
  5 |  15
  6 |  21
  7 |  28
  8 |  36
  9 |  45
 10 |  55
(10 rows)

It is so called "window functions", see documentation here: https://www.postgresql.org/docs/current/static/tutorial-window.html