SQL's Query adding values

Question

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?

What exactly do you want to do? Just add if COLUMN1 has a value of DOLAR and COLUMN0 has a value of B? Or are you trying to convert euro's or something? — Abe Miessler
– Abe Miessler, Commented Feb 12, 2013 at 23:30

sgeddes · Accepted Answer · 2013-02-13 00:05:40Z

2

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.

edited Feb 13, 2013 at 0:05

answered Feb 12, 2013 at 23:38

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

T I · Accepted Answer · 2013-02-12 23:39:43Z

1

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

answered Feb 12, 2013 at 23:39

T I

9,9634 gold badges31 silver badges51 bronze badges

Comments

user2001117 · Accepted Answer · 2013-02-13 05:45:58Z

0

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'

answered Feb 13, 2013 at 5:45

user2001117

3,7871 gold badge20 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL's Query adding values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related