Nested calculations in SQL SELECT-CodePudding

I have a SQLite table with buy and sell prices in different currencies. Something like

CREATE TABLE ins_and_outs 
(
    prod_id INTEGER NOT NULL,
    buy_price DECIMAL,
    buy_currency TEXT,
    buy_currency_EUR DECIMAL NOT NULL,
    sell_price DECIMAL,
    sell_currency TEXT,
    sell_currency_EUR DECIMAL
)

I need to calculate

the buy and sell prices in Eur (multiply 2 columns);
then take the previous values and calculate the difference (profit)
and finally take the difference and the calculated buy value and calculate the profit %

This requires referencing back to calculated values to calculate the next ones, hypothetically

SELECT buy_price * buy_currency_EUR AS buy_Eur, sell_price * sell_currency_EUR AS sell_Eur, sell_Eur - buy_Eur AS profit, profit / buy_Eur * 100 AS pct

Only this is not accepted and I don't want to repeat the calculations in every column if I can find a better solution.

This can be done easily assigning the SQL output to an external array or object of course, but I was looking into doing everything within the database, using one or more views.

I had a look at using sub-queries, but I would need to get the values from the same table row in the sub-query as the main query and I don't see how to do that, given that all the examples I found work with fixed values or values from other tables in the sub-query. I'm also thinking a solution using sub-queries would make the SQL statement very hard to read and troubleshoot in the future.

One alternative would be to create 2 views, the first calculates the sell prices in Eur and the second view takes the first as input and calculates the rest. Would this work? Also, can a nested view update all the way back to the original tables?

Another alternative would be to add the calculated columns to the table and trigger the calculations on UPDATE. This would use more disk space but require less CPU every time the data is accessed.

Most importantly, what would be the best solution for this case?

CodePudding user response：

So as mentioned in the comments in the SELECT statement section of your query you can not reuse the aliases you defined there:

SELECT buy_price * buy_currency_EUR AS buy_Eur, -- buy_Eur is an alias not a column 
       sell_price * sell_currency_EUR AS sell_Eur -- same here, 
       sell_Eur - buy_Eur AS profit, 
       profit / buy_Eur * 100 AS pct
FROM yourTable

So in order to solve your problem the easiest way is to repeat the calculations and make sure to use parenthesis:

SELECT buy_price * buy_currency_EUR AS buy_Eur, 
       sell_price * sell_currency_EUR AS sell_Eur, 
       (sell_price * sell_currency_EUR) - (buy_price * buy_currency_EUR) AS profit, 
       (((sell_price * sell_currency_EUR) - (buy_price * buy_currency_EUR)) / (buy_price * buy_currency_EUR) * 100) AS pct
FROM yourTable

The other way of doing it is using sub queries, but you would need more than one which doesn't make sense IMHO (I will show just one level):

SELECT buy_Eur, sell_Eur, 
       sell_Eur - buy_Eur as profit,
       (sell_Eur - buy_Eur) / buy_Eur * 100 as pct
FROM (
   SELECT buy_price * buy_currency_EUR AS buy_Eur, 
          sell_price * sell_currency_EUR AS sell_Eur 
     FROM yourTable
) as sub -- you need to name (alias) your subquery

EDIT: One more option is to create a view of your query and query it like a simple table:

CREATE VIEW yourViewName as
SELECT buy_price * buy_currency_EUR AS buy_Eur, 
       sell_price * sell_currency_EUR AS sell_Eur, 
       (sell_price * sell_currency_EUR) - (buy_price * buy_currency_EUR) AS profit, 
       (((sell_price * sell_currency_EUR) - (buy_price * buy_currency_EUR)) / (buy_price * buy_currency_EUR) * 100) AS pct
FROM yourTable;

Then just use:

select buy_Eur, sell_Eur, profit, pct 
from yourViewName