CODEWITHSUNDEEP
sql
koulritash
koulritash

Reputation: 21

Multiply two column values of two different tables

I have one table as

ID     DID        DValue
1      100        120
1      200        100
2      100        800
2      200        200

Another table is 

ID    AValue
1     100
2     200

I want to multiply Dvalue and Avalue and then divide by 1000.And there should be 2 values for same id because there are 2 DID's for one id.

O/P table

id   value
1    value of did 100 * avalue of id 1/1000
1    value of did 200 * avalue of id 1 /1000
2    value of did 100 * avalue of id 2/1000
2    value of did 200 * avalue of id 2 /1000

Upvotes: 0

Views: 1835

Answers (2)

Matt
Matt

Reputation: 15061

Just INNER JOIN the two tables.

SELECT t1.ID, ((t1.Dvalue * t2.Avalue) / 1000) AS Value
FROM yourtable1 t1
INNER JOIN yourtable2 t2 ON t1.Id = t2.Id

Table Script

CREATE TABLE yourtable1
    (`ID` int, `DID` int, `DValue` int)
;

INSERT INTO yourtable1
    (`ID`, `DID`, `DValue`)
VALUES
    (1, 100, 120),
    (1, 200, 100),
    (2, 100, 800),
    (2, 200, 200)
;


CREATE TABLE yourtable2
    (`ID` int, `AValue` int)
;

INSERT INTO yourtable2
    (`ID`, `AValue`)
VALUES
    (1, 100),
    (2, 200)
;

Output

ID  Value
1   12
1   10
2   160
2   40

SQL Fiddle: http://sqlfiddle.com/#!9/907b50/1/0

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Try this

SELECT t1.ID, t1.Dvalue*t2.Avalue/1000 from table1 t1 inner join
table2 t2 on t1.Id=t2.Id

Upvotes: 0

Related Questions