Loading

how to multiply value from first table with second table

how to multiply value from first table with second table

Kiresh Gangariya

11y1.5k

Hi

TABLE A

ID	Name	Rate	Reference Id
1	a	5	1
2	b	4	1
3	c	2	2
4	d	3	2

TABLE B

tID	Rate	Reference Id	date
1	2	1	01/01/2010
2	3	2	02/02/2014

Now

from the TABLE B get Value of Rate and multiply it with Rate of TABLE A which having TABLE BA.Reference Id = TABLE B .Reference Id.

The Result Should be like this,

ID	Name	Rate	Reference Id
1	a	(5*2) = 7	1
2	b	(4*2) = 8	1
3	c	(2*3) = 6	2
4	d	(3*3) = 9	2

Thanks,

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi11y ago
Hi,
Consider both table 's Rate is decimal / float type.

SELECT A.ID, A.Name, (A.RATE * B.RATE) as RATE from TABLEA a
INNER JOIN TABLEB b ON a.ReferenceId = b.ReferenceId
hope this will help you.
0
Vikrant More11y ago
Kiresh in the Result should be for Rate did you mean this 5*2 = 10 ?

script for this as below,

IF OBJECT_ID('TEMPDB..#TableA') IS NOT NULL
    DROP TABLE TEMPDB..#TableA

IF OBJECT_ID('TEMPDB..#TableB') IS NOT NULL
    DROP TABLE TEMPDB..#TableB

CREATE TABLE #TableA
(
    ID INT IDENTITY(1,1),
    NAME CHAR(1),
    RATE INT,
    ReferenceID INT
)

CREATE TABLE #TableB
(
    tID INT IDENTITY(1,1),
    RATE INT,
    ReferenceID INT,
    date date
)

INSERT INTO #TableA VALUES('a',5,1),('b',4,1),('c',2,2),('d',3,2)
INSERT INTO #TableB VALUES(2,1,'2010-01-01'),(3,2,'2014-02-02')

SELECT A.ID,A.NAME,'('+CONVERT(CHAR(1),A.RATE)+'*'+CONVERT(CHAR(1),B.RATE)+') = '+ CONVERT(CHAR(2),A.RATE * B.RATE) AS RATE,A.ReferenceID
FROM #TableA A
JOIN #TableB B ON A.ReferenceID = B.ReferenceID
0
Vinodh Narayanan11y ago
ckeck out this link it may help you

http://stackoverflow.com/questions/21266054/multiplying-column-by-weight-in-another-table-requiring-a-second-select-and-summ

mark if accepted as answer
0