mysql - 使用 GROUP BY 语句对行求和

Question

我在不同的市场销售了多种产品，我需要计算每个产品的总和。开始:

mysql> SELECT title, partner_share_currency, us_earnings_usd, cad_earnings_cad 
        FROM raw_financials WHERE title LIKE "%Gamers%";
+--------+------------------------+-----------------+------------------+
| title  | partner_share_currency | us_earnings_usd | cad_earnings_cad |
+--------+------------------------+-----------------+------------------+
| Gamers | USD                    |          3.2500 |           0.0000 |
| Gamers | CAD                    |          0.0000 |           4.0000 |
| Gamers | USD                    |          4.5000 |           0.0000 |
+--------+------------------------+-----------------+------------------+

这就是我目前为获得 GROUP BY 标题所做的事情:

mysql> SELECT title, us_earnings_usd, cad_earnings_cad 
        FROM raw_financials WHERE title LIKE "%Gamers%" GROUP BY title;
+--------+-----------------+------------------+
| title  | us_earnings_usd | cad_earnings_cad |
+--------+-----------------+------------------+
| Gamers |          3.2500 |           0.0000 |
+--------+-----------------+------------------+

如您所见，它不会对值行求和。我将如何更改 SELECT 语句，以便它总结值行，给我:

+--------+-----------------+------------------+
| title  | us_earnings_usd | cad_earnings_cad |
+--------+-----------------+------------------+
| Gamers |          7.7500 |           4.0000 |
+--------+-----------------+------------------+

Answer 1

您需要一个聚合 SUM() 来对您的行求和。您只有一个 GROUP BY。

SELECT
  title, 
  partner_share_currency,
  SUM(us_earnings_usd) AS usd,
  SUM(cad_earnings_cad) AS cad
FROM raw_financials 
WHERE title LIKE '%Gamers%'
GROUP BY title, partner_share_currency

请注意，MySQL 将允许您在 GROUP BY 子句中仅包含 title，而大多数其他 RDBMS 还要求您在其中列出其他非聚合列分组依据。具体来说，就是 partner_share_currency