CODEWITHSUNDEEP
sqlsql-servert-sql
TooManyProblems
TooManyProblems

Reputation: 3

Making a new column based on a derived column sql query?

So I have a single table called TOY_DETAILS within are ID,Date,Toy_obje,currency(can be USD, GBP or EUR) and budget.

Now I want to make a sql query that will return everything along with

  1. A computed column that will have the budget in terms of dollars(so conversion rate is like 1 pound = 1.5 dollars and 1 euro = 1.2)
  2. Another computed column that will have the total budget in dollars.

What I've tried.

SELECT ID, Date,Toy_object, currency, budget, 
CASE currency
WHEN 'GBP' THEN budget*1.5
WHEN 'EUR' THEN budget*1.2
ELSE budget
END AS USD_Value
(SELECT SUM(USD_Value) FROM PROJECT_DETAILS) AS Total_Budget
FROM PROJECT_DETAILS

My problem is 2). Where I want a Total_Budget to be the SUM of the column USD_Value, however I can't type 'SUM(USD_Value)' cause that 'column doesn't exist' ?

Thanks in advance,

Upvotes: 0

Views: 56

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You have to repeat the CASE expression inside the SUM:

SELECT ID, Date,Toy_object, currency, budget, 
       CASE currency 
          WHEN 'GBP' THEN budget*1.67 
          WHEN 'EUR' THEN budget*1.3 
       ELSE budget END AS USD_Value,
       SUM(CASE currency 
              WHEN 'GBP' THEN budget*1.67 
              WHEN 'EUR' THEN budget*1.3 
              ELSE budget 
            END) OVER() AS Total_Budget 
FROM PROJECT_DETAILS

Upvotes: 2

Related Questions