Sum the "previous row" value with the existing row value in the SELECT statement
I want to sum the previous value with the existing value on this line.
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')
this code will look like this: 
the table could be like this:
Thank.
source to share
Here's an example of what you need (hopefully):
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)
These are so called "window functions", see the documentation here: https://www.postgresql.org/docs/current/static/tutorial-window.html
source to share
You can use Window functions with the Frame clause.
If you want to use SUM with the previous line, follow these steps:
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;
Pay attention to ROWS 1 PRECEDING
.
If you want to use SUM with all the previous lines (total amount), you would 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;
Pay attention to 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
- main actor:
-
SUM(p.price * od.qty)
- calculates the price per day -
SUM(SUM(...)) OVER (...)
- sums up several prices in several days -
PARTITION BY o.partner_id
- required in order to keep SUM within the boundspartner_id
-
ORDER BY o.partner_id, o.date
- it is required to order the lines in the section by date -
ROWS 1 PRECEDING
- to include the previous line in SUM along with the current line
Full 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;
source to share