mysql - 不同组的聚合函数

Question

考虑一个带有列的表“集合”

region | name | amount 
R1       Ron    100
R1       Ron    200
R1       John   200
R1       John   300
R2       Ron    100
R2       Ron    100
R2       Ron    100
R2       John   100

输出:

region |Region Total| Region Count | name | Total| Count
R1              800              4    Ron     300      2
R1              800              4   John     500      2
R2              400              4    Ron     300      3
R2              400              4   John     100      1

我需要做的是按地区计算金额总和，然后按名称计算金额总和以及按地区和名称排列的馆藏数量

所以查询是这样的

select region, (Select sum(amount) from Collections A where A.region = B.region) as 'Region Total', 
(Select count(amount) from Collections A where A.region = B.region) 'Region Count',
 name,
sum(amount) as 'amount' , 
count 
from Collections B 
group by region, name

问题:如何在不使用这些子查询的情况下做到这一点

Answer 1

太简单了？ (看来我错了)

SELECT region, name, SUM(amount), COUNT(*)
FROM tab
GROUP BY region, name;

已更新

我认为以下查询包含您需要的所有信息。现在，是时候在客户端转换显示格式了。

SELECT region, name, SUM(amount), COUNT(*)
FROM tab
GROUP BY region, name

UNION ALL

SELECT region, NULL, SUM(amount), COUNT(*)
FROM tab
GROUP BY region

+--------+------+-------------+----------+
| region | name | SUM(amount) | COUNT(*) |
+--------+------+-------------+----------+
| R1     | John |         500 |        2 |
| R1     | Ron  |         300 |        2 |
| R2     | John |         100 |        1 |
| R2     | Ron  |         300 |        3 |
| R1     | NULL |         800 |        4 |
| R2     | NULL |         400 |        4 |
+--------+------+-------------+----------+

更新2

SELECT x.region, y.s2, y.c2, x.name, x.s1, x.c1
FROM
(
    SELECT region, name, SUM(amount) s1, COUNT(*) c1
    FROM tab
    GROUP BY region, name
) x INNER JOIN
(
    SELECT region,  SUM(amount) s2, COUNT(*) c2
    FROM tab
    GROUP BY region
) y ON x.region = y.region
+--------+------+----+------+------+----+
| region | s2   | c2 | name | s1   | c1 |
+--------+------+----+------+------+----+
| R1     |  800 |  4 | John |  500 |  2 |
| R1     |  800 |  4 | Ron  |  300 |  2 |
| R2     |  400 |  4 | John |  100 |  1 |
| R2     |  400 |  4 | Ron  |  300 |  3 |
+--------+------+----+------+------+----+

如果您的实际查询非常复杂或有很多行，我认为 UNION 或 WITH ROLLUP 更适合您，即使查询输出格式不同具有显示格式。