CODEWITHSUNDEEP
mysqlsqldatabasesum
Andrew Tsay
Andrew Tsay

Reputation: 1953

How do I multiply and sum all values in two SQL tables

Let's say we have two tables.

1   0.73
2   0.13
3   0.14

and

1   1
2   1.465
3   1.181

I want to somehow loop through them in order to perform

(0.73*1) + (0.13*1.465) + (0.14 * 1.181) = 1.08579

Or perhaps are there any SQL queries that would help with this?

Update: Here's a sample query. No syntax error, but doesn't seem to be returning anything though.

SELECT 
    sum(`racemean`.male * `racehazard`.male) 
FROM 
    `racemean` 
JOIN 
    `racehazard` ON `racemean`.male = `racehazard`.male

I'm not sure what format to show sample data, but this is exported from phpMyAdmin.

http://sqlfiddle.com/#!2/b3ac2

Upvotes: 4

Views: 7475

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

If the tables are connected by a column that can be used to join, the query below should work.

select sum(t1.col2 * t2.col2)
from t1 join t2 on t1.col1 = t2.col1

Fiddle with sample data

Fiddle with the data from question

Upvotes: 7

Related Questions