Postgres：选择值的总和，然后再次求和

Question

我尝试了很多方法，但始终找不到正确的解决方式。 如果我在Postgres中选择值并对它们求和，则如下所示：

SELECT name,sum(size) as total
FROM mytable group by name order by name;

我该如何修改这段代码，使它还可以对所有值进行求和并输出总和？我觉得需要用到子查询，但该如何实现呢？

- Anatol

Answer 1

试试这个：

SELECT sum(a.total)
FROM (SELECT sum(size) as total
      FROM mytable group by name) a

更新：抱歉，我没有看到您希望在同一个查询中完成所有操作。这就是为什么greg的答案更好的原因。不过，如果您使用的是 PostgreSQL 版本 >= 9，则还有另一种可能性：

WITH mytableWith (name, sum) as
     (SELECT name, sum(size)
      FROM mytable
      GROUP BY name)
SELECT 'grand total' AS name, 
       sum(sum) AS sum
FROM mytableWith
UNION ALL
SELECT name, sum
FROM mytableWith

- doctore

Answer 2

我会在POSTRGESQL上使用ROLLUP函数:

SELECT name,sum(size) as total
FROM mytable 
group by ROLLUP(name )
order by name;

这将为您提供聚合的任何值的总计，并且也可用于聚合多个列。

希望这有所帮助！

- oyeoyeoye

Answer 3

如果您希望在同一个SELECT中获取所有结果，您可以像这样做：

SELECT 
  'grand total' AS name, 
   sum(size) AS sum 
FROM 
  mytable 
UNION ALL 
SELECT 
  name, 
  sum(size) AS sum 
FROM 
  mytable 
GROUP BY 
  name;

希望我能帮到你...

- greg

Answer 4

好的，这可以帮助你:

select sum(innerselect.innertotal) as outertotal from 
    (select sum(size) as innertotal from mytable group by name) as innerselect

- hellur