How to find the SUM (product_price) from the combination of two tables using JOIN in SQL

Question

I have two tables:

table A

invoicedate | client_id | shipment_id | weight | product_price

table B

invoicedate | client_id | shipment_id | weight

I am trying to create a new table C, which calculates a new column with the sum(product_price): So I am expecting something like: (Each client can have many shipments with the same shipment_id but the product_price changes for each distinct shipment_id ) (In table B some weights are missing)

Example: Table A

invoicedate | client_id | shipment_id | weight | product_price  
12/10/19         1111        888           48        36            
13/11/19         2222        111           30        45
12/10/19         1111        888           48        125         
12/10/19         1111        888           48        127.2

Table B

invoicedate | client_id | shipment_id | weight 
12/10/19         1111        888           48                    
13/11/19         2222        111           30       
12/10/19         1111        888           -                  
12/10/19         1111        888           48

New Table C

distinct(client_id)|invoicedate |  distinct(weight) | total_sum(product_price)

    1111           |   12/10/19 |       1111        |    (36+125+127.2)                   
    2222           |   13/11/19 |      2222         |        45      

My code:

    create table C as 
    select A.invoicedate,A.shipment_id,A.weight,sum(A.product_price) as sum_product_price
    from A
    right join B on B.id=A.id
    group by  A.id, A.weight

The sum(product_price) is calculated wrong and I dont understand why..

Answer 1

select A.client_id,A.invoicedate,A.weight,SUM(product_price)  from Table_A A
group by A.client_id,A.invoicedate ,A.weight 

Answer 2

If you want to create a table from compiling two tables. So, this Reference will help you.

CREATE TABLE Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table):

Example

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

So, after I tried this followed instructions, I tried by myself and that what I got

CREATE 'C' TABLE:

create table C AS
SELECT A.client_id as distinct(client_id), A.invoicedate, B.weight AS distinct(weight), SUM(product_price) AS total_sum(product_price)
FROM A, B
FULL OUTER JOIN A.client_id = B.client_id
group by A.client_id, B.invoicedate;

SELECT STATEMENT:

SELECT * FROM C;

OUTPUT

distinct(client_id)|invoicedate |  distinct(weight) | total_sum(product_price)

    1111           |   12/10/19 |       1111        |       133                
    2222           |   13/11/19 |       2222        |       133    

Reference: SQL TABLE Keyword