I want to use the result of query to create new column in order to to find the change of freight to the revenue
The original question is What is the impact on revenue of the additional 10% in freight. I am currently using Northwind dataset
select sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight*1.1) as New, sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight) as Old, New-Old from "Order Details", Orders;
SQL query does not work as it does not recognize new variable “New” and “Old”
I have updated the version that suggessted but it generate error messange
NexusDB: Query534984250: Query execution failed: NexusDB: Correlation name for subquery expected:
SELECT New,Old,New-Old FROM (select sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight*1.1) as New, sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight) as Old FROM "Order Details" INNER JOIN "Orders" ON ("Order Details".OrderID = Orders.OrderID) ) ;
Advertisement
Answer
You can use the columns New and Old after they are run in a subquery
But your (sub)query will not give you a correct result as
From `Order Details`, `Orders`
is a cross join between both tables and would return too many rows to give you the expected result
So you would
FROM `Order Details` od INNER JOIN `Orders` o ON o.id = od.order_REf_id
And then you still must check if the result is what you are looking for
SELECT New,Old, New-Old FROM (select sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight*1.1) as New, sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight) as Old from `Order Details`, `Orders` ) t1
In end effect it will lokk like
SELECT New,Old, New-Old FROM (select sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight*1.1) as New, sum(UnitPrice*Quantity*(1-Discount)) - sum(Freight) as Old FROM `Order Details` od INNER JOIN `Orders` o ON o.id = od.order_REf_id` ) t1