Swee Hong - 3 months ago 8
SQL Question

# Mysql Php Sum() Multiplication?

I have 2 table.

`t1`
AND
`t2`

Data as below
`t1`
,
`kid`
is planID,
`uid`
is Userid,
`buytime`

``````+---------+-------------+-------------+---------------+
+---------+-------------+-------------+---------------+
| 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`
?

Just use `sum` as you provided -- this will result in 40700:
``````select sum((t1.buytime1 * t2.costb1) + (t1.buytime2 * t2.costb2))
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.