sql - How to fetch data from two related tables and put in 3rd table

Question

Total amount in Table 3 depends on Table1(Ship_chr) and multiple table2(quant*rate) entries.

what will be sqlite3 query for achieving this?

table 1:
order_ID  date   buyer  ship_chr  
001       01/01  abc    15        
002       05/01  xyz    10       

table 2:
order_ID  prod    quantity   rate
001       pen     50         2
001       paper   25         1
001       pin     50         2
002       paper   25         1
002       pen     100        2

table 3:
order_ID  total_amount
001       240
002       235

Answer 1

You coud use inner join , sum and group by

select a.order_ID, sum(a.quantity  * a.rate) + b.ship_chr as total_amount
from table2 a 
inner join table1 b on a.order_ID = b.order_ID
group by a.order_ID,  b.ship_chr

or using a suquery for the sum

select t1.order_ID, t1.tot + b.ship_chr total_amount
from table1 b
inner join (
    select a.order_ID, sum(a.quantity  * a.rate)  tot
    from table2 a 
    group by a.order_ID
) t1 on t1.order_ID = b.order_ID