[SOLVED] Filter a column twice to make two different columns and join them to the columns of onother table

Filter a column twice to make two different columns and join them to the columns of onother table

I want to filter a numeric column according to 2 different criteria (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

Solution

You can try the following query. Working example here

    WITH purchases AS (
   
    SELECT item, purch_date_1 AS purch_date, 
           SUM(CASE WHEN purch_date_1 BETWEEN '2024-06-01' AND '2024-06-30' THEN purch_amt_1 ELSE 0 END) AS june_purch,
           SUM(purch_amt_1) AS bal_june
    FROM table_purch1
    WHERE purch_date_1 <= '2024-06-30'
    GROUP BY item, purch_date_1

    UNION ALL

   
    SELECT item2 AS item, purch_date_2 AS purch_date, 
           SUM(CASE WHEN purch_date_2 BETWEEN '2024-06-01' AND '2024-06-30' THEN purch_amt2 ELSE 0 END) AS june_purch,
           SUM(purch_amt2) AS bal_june
    FROM table_purch2
    WHERE purch_date_2 <= '2024-06-30'
    GROUP BY item2, purch_date_2
)

SELECT item, 
       purch_date, 
       SUM(june_purch) AS june_purch, 
       SUM(bal_june) AS bal_june
FROM purchases
GROUP BY item, purch_date
ORDER BY purch_date;