CODEWITHSUNDEEP
sqlsql-server
user2023944
user2023944

Reputation: 49

SQL's Query adding values

I hope you can help me, in this sql query. (SQL SERVER) I have these tables

TABLE1
COLUMN0 COLUMN1 COLUMN2 COLUMN3
A        DOLAR    2.5   18/02/2013
A        DOLAR    1.5   18/02/2013
B        EURO     22    19/02/2013
B        DOLAR    4.5   19/02/2013
B        DOLAR    1.3   18/02/2013
C        EURO     0.5   19/02/2013

TAB_RATES
COLUMN0    COLUMN1
18/02/2013 1.35
19/02/2013 1.36

I want to add all the 'B' values (COLUMN2) in dollars But i have some that are in euros Column3 in TABLE1 shows dolar value in that date (FK_TAB_RATES)

What would be the sql query?

Upvotes: 0

Views: 68

Answers (3)

user2001117
user2001117

Reputation: 3777

try this:

SELECT SUM (T.Column2 *
    CASE 
        WHEN T.Column1 = 'Euro'
        THEN TR.Column1
        ELSE 1.0
    END) SummedAmt
FROM Table1 T
    JOIN Tab_Rates TR ON T.Column3 = TR.Column0
WHERE T.Column0 = 'B'

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

My guess is your Tab_Rates table is the currency multiplier for that given day. If so, something like this might work:

SELECT SUM (T.Column2 *
    CASE 
        WHEN T.Column1 = 'Euro'
        THEN TR.Column1
        ELSE 1.0
    END) SummedAmt
FROM Table1 T
    JOIN Tab_Rates TR ON T.Column3 = TR.Column0
WHERE T.Column0 = 'B'

And here is a SQL Fiddle to demo it.

This produces 35.72. Doing it manually, 4.5 + 1.3 + 22(1.36) = 35.72

Good luck.

Upvotes: 2

T I
T I

Reputation: 9933

Try something like this (untested)

select t1.col0,
    sum (case when t1.col1 = 'euro' then t1.col1 * rate.r
              else t1.col1 end) as s
from t1
inner join rate on rate.r = t1.col3
group by col0

Upvotes: 1

Related Questions