getting the sum of products between two columns in SQL-CodePudding

My situation is this: I have an order_details table containing entities representing the "content" of an orders, each row has

a product_id column ---> foreign key referencing the type of product
an order_id column ----> foreign key represents which order the product belongs to
a qty column ----------> representing the quantity of the product present in the order

each product row instead has:

a product name column ----> representing the product name
a co2_value --------------> representing the value of co2 removed buying that product

each table (orders table too) has his own id column as primary key for the rows.

the problem is the following:

I need a sql query to find the total co2_value of an order adding all the co2_values of the products belonging to a specific order, multiplied by their quantity.

I tried this query first:

SELECT SUM(co2_value) FROM products 
                    INNER JOIN order_details 
                    ON products.id = order_details.product_id AND order_details.order_id = ". $this->id;

the problem with this is that i'am getting only the sum of the co2_values of the product without multiplying them by the quantity, is there a way to do that in a single query?

I'll leave the link to migration here

CodePudding user response：

select
    sum(co2_value * qty)
from
    products
inner join order_details on
    products.id = order_details.product_id
    and order_details.order_id = ". $this->id;

You can simply select the co2_value with respect to its quantity by multiplying it using the above query