[Python] Filter a column twice to make two different columns and join them to the columns of...

I updated my question and added some data to make it easer to answer.

I want to filter a numeric column according to 2 different criterias (2 periods of time) and output two different numeric columns, one for each period. Then, I want to join them to the columns of other tables (about 5). Those columns have the same type as the first one. I will give the codes for two table to make to simplify that.

Here is what I tried:

SELECT item, purch_date_1 , SUM(purch_amt_1) AS june_purch FROM table_purch1

WHERE table_purch1.purch_date_1 BETWEEN '2024-06-01' AND '2024-06-30'

GROUP BY purch_date_1, item

FULL JOIN (

SELECT SUM(purch_amt1) AS bal_juin FROM table_purch1

WHERE table_purch1.purch_date_1 \<= '2024-06-30'

GROUP BY purch_date_1 ) AS T1 ON purch_date_1

UNION

SELECT item2, purch_date_2 , SUM(purch_amt2) AS june_purch2

FROM table_purch2

WHERE table_purch2.purch_date_2 BETWEEN '2024-06-01' AND '2024-06-30' # For june only

GROUP BY purch_date_2

FULL JOIN

(SELECT SUM(purch_amt2) AS bal_june2 FROM table_purch2

WHERE table_purch2.purch_date_2 \<= '2024-06-30' # From the start to June(the report month)

GROUP BY purch_date_2 ) AS T2 ON purch_date_2 ;


I use Python for my application and postgresql for the db.

I am new to sql. I tried other methods, I still do not know how to come to the expected result.

I pulled data from those two tables:

table_purch1

Date item amount "2024-05-16" "A" 100.0 "2024-06-05" "B" 150.0 "2024-06-05" "B" 200.0 "2024-06-12" "D" 250.0 "2024-06-12" "D" 300.0

table_purch2

Date item amount "2024-05-16" "A" 100.0 "2024-06-05" "B" 100.0 "2024-06-12" "D" 100.0

Here is an example of the result I am expecting:

item purch_date june_purch bal_june
A "2024-05-16" 0 200.0
B "2024-06-05" 450.0 450.0
C "2024-06-11" 500.0 500.0
D "2024-06-12" 650.0 650.0


Thanks in advance for your answers.

Continue reading...