Join tables and SUM columns for each individual row with some rows are treated as columns

Question

I have two tables:

table1

id1 | total_amount| final_amount| p_id --------------------------------------- 1 | 500 | 505 | 10 2 | 700 | 710 | 20 3 | 400 | 400 | 10 4 | 450 | 460 | 20 5 | 300 | 300 | 10 

table2

 id2 | month | amount | id1 ------------------------------ 1 | jan | 300 | 1 2 | feb | 200 | 1 3 | jan | 200 | 2 4 | feb | 500 | 2 5 | feb | 400 | 3 6 | jan | 150 | 4 7 | feb | 300 | 4 8 | jan | 300 | 5 

here the value of the tatal_amount column is equal to the sum (quantity) by condition as id1 while final_amount receives from total_amount + (some dummy value)

therefore, the result obtained from this table is as follows.

 id3 | p_id | jan | feb | total ------------------------------- 1 | 10 | 600 | 600 | 1205 2 | 20 | 350 | 800 | 1170 

here the value is in jan, the columns feb are the sum of the sum in table2, and the value in the final column is the sum of final_amount in table1, and they are all p_id so this is a query for the output above. if you know, then answer ...

Answer 1

You do not specify an RDBMS.

Running in SQL Server

 WITH table1(id1, total_amount, final_amount, p_id) AS (SELECT 1,500,505,10 UNION ALL SELECT 2,700,710,20 UNION ALL SELECT 3,400,400,10 UNION ALL SELECT 4,450,460,20 UNION ALL SELECT 5,300,300,10), table2(id2, month, amount, id1) AS (SELECT 1,'jan',300,1 UNION ALL SELECT 2,'feb',200,1 UNION ALL SELECT 3,'jan',200,2 UNION ALL SELECT 4,'feb',500,2 UNION ALL SELECT 5,'feb',400,3 UNION ALL SELECT 6,'jan',150,4 UNION ALL SELECT 7,'feb',300,4 UNION ALL SELECT 8,'jan',300,5), t1 AS (SELECT p_id,SUM(final_amount) AS total_amount FROM table1 GROUP BY p_id), t2 AS (SELECT p_id, SUM(CASE WHEN month = 'jan' THEN amount END) AS jan, SUM(CASE WHEN month = 'feb' THEN amount END) AS feb FROM table2 JOIN table1 ON table1.id1 = table2.id1 GROUP BY p_id) SELECT Row_number() OVER (ORDER BY (SELECT 0)) AS id3,t1.p_id,jan,feb, total_amount FROM t1 JOIN t2 ON t1.p_id = t2.p_id