Getting the Sum of Products Between Two Columns in Sql
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...
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
1 Answer
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
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
