Add new column in SQL query-CodePudding

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)
      )  ;

CodePudding user response：

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