Swee Hong Swee Hong - 4 months ago 19
SQL Question

Mysql Php Sum() Multiplication?

I have 2 table.

t1
AND
t2


Data as below
t1
,
kid
is planID,
uid
is Userid,
buytime
is buy quantity.

+---------+-------------+-------------+---------------+
| kid | uid | buytime1 | buytime2 |
+---------+-------------+-------------+---------------+
| 1 | 1 | 10 | 5 |
| 1 | 2 | 1 | 0 |
| 1 | 3 | 3 | 5 |
| 2 | 4 | 1 | 10 |
| 3 | 5 | 20 | 6 |
+---------+-------------+-------------+---------------+


t2
Data:

+---------+-----------+-----------+
| ID | costb1 | costb2 |
+---------+-----------+-----------+
| 1 | 100 | 200 |
| 2 | 300 | 500 |
| 3 | 1000 | 2000 |
+---------------------------------+


I would like to do the
SUM
in mysql like:

SUM buytime1 * costb1 + buytime2 * costb2 Where t1.kid = t2.id


$query = DB::query("SELECT SUM(something like buytime1 * costb1 + buytime2 * costb2) FROM ".DB::table('t1')." t1 LEFT JOIN ".DB::table('t2')." t2 ON (t1.kid = t2.id)");


so the final result should be
40700


10 * 100 + 5 * 200 = 2000
1 * 100 + 0 * 200 = 100
3 * 100 + 5 * 200 = 1300
1 * 300 + 10 * 500 = 5300
20 * 1000 + 6 * 2000 = 32000


So what should i edit my coding in the
query
?

Answer

Just use sum as you provided -- this will result in 40700:

select sum((t1.buytime1 * t2.costb1) + (t1.buytime2 * t2.costb2))
from t1 
  join t2 on t1.kid = t2.id

Not sure if you need the outer join - if so, then you need to define what those missing values should equal. Using coalesce though should help there if needed.