来自不同表的求和值从另一个表获取值

Question

I'm trying to sum coins from m_z_analytics but replace these values with cpc. For example:

0.01 -> 0.09

0.1 -> 0.1

0.08 -> 0.09

0.1 -> 0.1

site must have same number as id from m_b_browsing_live. Used id must be: 1,2 (see result below).

Tables:

m_z_analytics

id|site_id|coins|
 1|      1| 0.01|
 2|      2|  0.1|
 3|      1| 0.08|
 4|      2|  0.1|
 3|      3|  0.2|


m_b_browsing_live

id| cpc|
 1|0.09|
 2| 0.1|
 3| 0.5|

The final result should be: 0.38 (0.09 + 0.1 + 0.09 + 0.1)

I've tried so far:

SELECT SUM(a.coins) AS money FROM m_z_analytics a
LEFT JOIN m_b_browsing_live b ON b.id=a.site_id
WHERE CONCAT(',', `a.site_id`, ',') REGEXP ',(1|2),' AND a.coins=b.cpc

SELECT ROUND(SUM(a.coins),2) AS money FROM m_z_analytics a
LEFT JOIN m_b_browsing_live b ON b.id=a.site_id
WHERE a.site_id IN(1,2) AND a.coins=b.cpc;

Answer 1

You should be summing b.cpc, not a.coins. And you shouldn't have a.coins = b.cpc in the WHERE clause, since they're not generally equal (if they were, you wouldn't need to join the other table).

SELECT SUM(b.cpc) AS money
FROM m_z_analytics AS a
JOIN m_b_browsing_live AS b on a.site_id = b.id
WHERE a.site_id IN (1, 2)