CODEWITHSUNDEEP
sqlitesumunionwindow-functions
Mohammad Yasin
Mohammad Yasin

Reputation: 31

How to sum two column row elements in extra column?

I'm FULL OUTER JOIN two tables Givens in db<>fiddle. The first table name is product_purchase and the second table name is sales_return. They are as follows:

SELECT *FROM 'product_purchase'

date invoice_no product_id purchase_quantity price
2021-01-01 10:00:00 p-101 A-1 100 100
2021-01-02 11:00:00 p-102 A-1 90 90
2021-01-03 12:00:00 p-103 A-1 200 200
2021-01-04 13:00:00 p-104 A-1 250 250

SELECT *FROM 'sales_return'

date invoice_no product_id sales_return_quantity price
2021-01-01 10:00:00 r-101 A-1 10 10
2021-01-04 13:00:00 r-104 A-1 25 25

I wanted to sum product_purchase table column purchase_quantity row elements and sales_return table column sales_return_quantity row elements in extra column total. Following code Givens in db<>fiddle, I write to do this.

SELECT pp.`date`, pp.`invoice_no`, pp.`product_id`, pp.`purchase_quantity`, sr.`sales_return_quantity`, sum(pp.`purchase_quantity`) OVER (ORDER BY pp.date) AS total
FROM  product_purchase pp
       LEFT JOIN sales_return sr
          ON pp.product_id = sr.product_id AND pp.product_id != sr.product_id
UNION ALL
SELECT sr.`date`, sr.`invoice_no`, sr.`product_id`, pp.`purchase_quantity`, sr.`sales_return_quantity`, sum(sr.`sales_return_quantity`) OVER (ORDER BY sr.date) AS total
FROM   sales_return sr
       LEFT JOIN product_purchase pp
         ON pp.product_id = sr.product_id AND pp.product_id != sr.product_id
WHERE  pp.product_id IS NULL
ORDER BY pp.`date`

It sum total column table-wise separately:

total
100
10
190
390
640
35

It need the following result:

total
100
110
200
400
650
675

Upvotes: 1

Views: 82

Answers (1)

forpas
forpas

Reputation: 164089

Instead of a FULL join use UNION ALL and SUM() window function:

SELECT date, invoice_no, product_id, purchase_quantity, sales_return_quantity,
       SUM(COALESCE(purchase_quantity, 0) + COALESCE(sales_return_quantity, 0))
       OVER (ORDER BY date, sales_return_quantity IS NOT NULL) total
FROM (
  SELECT date, invoice_no, product_id, purchase_quantity, null sales_return_quantity
  FROM product_purchase
  UNION ALL
  SELECT date, invoice_no, product_id, null purchase_quantity, sales_return_quantity
  FROM sales_return
)
ORDER BY date, sales_return_quantity IS NOT NULL

See the demo.

Upvotes: 1

Related Questions