mysql:如何从一个表中提取多个表并在每个表中分组，然后组合成一个结果？

Question

我的表格是这样的:

+------+------+------+
| prov | amnt | type |
+------+------+------+
| on   |   10 | 1    |
| on   |   10 | 1    |
| on   |   12 | 2    |
| ab   |   10 | 1    |
| ab   |   12 | 2    |
| ab   |   12 | 2    |
| qc   |   12 | 2    |
| qc   |   13 | 3    |
| qc   |   13 | 3    |
+------+------+------+

我想返回一个表，其中对于每个 prov 都有一个按类型分组的摘要，其中包含每种类型的总和和计数，如下所示:

+------+-------+------+------+
| prov | total | type | count|
+------+-------+------+------+
| on   |    20 | 1    |     2| 
| on   |    12 | 2    |     2|
| ab   |    10 | 1    |     1|
| ab   |    24 | 2    |     2|
| qc   |    12 | 2    |     1|
| qc   |    26 | 3    |     2|
+------+-------+------+------+

基本上，如果我为每个省执行三个手动输入的查询，我将使用三个不同的查询在三个不同的表中获取数据。

SELECT prov, SUM(fine) AS total, code, COUNT(code) AS COUNT 
FROM table 
WHERE prov = 'on' 
GROUP BY code;

   +------+-------+------+------+
   | prov | total | type | count|
   +------+-------+------+------+
   | on   |    20 | 1    |     2| 
   | on   |    12 | 2    |     2|
   +------+-------+------+------+

Answer 1

将prov添加到group by:

SELECT prov, SUM(fine) AS total, code, COUNT(code) AS COUNT 
FROM table 
GROUP BY prov, code;