mysql - 如何使用 SQL 对多行中的列值求和

Question

我在 MySQL 中有一个表，其中列出了用户的股票购买情况（因此同一股票有多行具有不同的价格和数量）。

我想知道如何仅列出所有股票的总和以及在该股票上花费的总金额的独特股票。

例如，这是原始表格，

+------------+--------+-----------+
| ticker     | shares | price     |
+------------+--------+-----------+
| AMARAJABAT |      5 |  720.0000 |
| AMARAJABAT |      5 |  732.0000 |
| ENDURANCE  |      2 | 1650.0000 |
| ENDURANCE  |      2 | 1750.0000 |
| EXIDEIND   |     21 |  163.5000 |
| EXIDEIND   |     21 |  172.1000 |
+------------+--------+-----------+

并选择表格以获取股票*价格，

SELECT ticker,shares,price,shares*price AS spent FROM bought ORDER BY ticker;

+------------+--------+-----------+------------+
| ticker     | shares | price     | spent      |
+------------+--------+-----------+------------+
| AMARAJABAT |      5 |  720.0000 |  3600.0000 |
| AMARAJABAT |      5 |  732.0000 |  3660.0000 |
| ENDURANCE  |      2 | 1650.0000 |  3300.0000 |
| ENDURANCE  |      2 | 1750.0000 |  3500.0000 |
| EXIDEIND   |     21 |  163.5000 |  3433.5000 |
| EXIDEIND   |     21 |  172.1000 |  3614.1000 |
+------------+--------+-----------+------------+

而且我想将股票列的值（对于同一只股票）和每个列的花费金额相加。即输出，

+------------+-------------+------------+
| ticker     | SUM(shares) | SUM(spent) |
+------------+-------------+------------+
| AMARAJABAT |          10 |  7260.0000 |
| ENDURANCE  |           4 |  6800.0000 |
| EXIDEIND   |          42 |  7047.6000 |
+------------+-------------+------------+

但似乎无法弄清楚如何将多列和多行相加ticker。

提前致谢！

标签： mysqlsqlsummariadb

Answer 1

只是分组：

select ticker , sum(shares), sum(spent)
from bought
group by ticker
order by ticker;