Postgres: select the sum of the values ​​and then sum it up again.

Question

I tried a lot, but can not find the right way. If I select the values ​​in Postgres and summarize them, it will look like this:

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

How can I change this so that it also sums all the values? I think I need subtasks, but how?

Answer 1

Try the following:

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

UPDATE Sorry, I did not read that you want all in one request. For this reason, greg's answer is better. However, another possibility, if you have a version of 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 

Answer 2

If you want all the results with the same SELECT, you could do something like

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

Hope this helps ...

Answer 3

Ok, this should help you:

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