Postgres: select the sum of values and then sum th

Question

I tried a lot but can´t find the right way. If I select values in Postgres and sum them it looks like this:

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

How can I alter this so it also sum all values in total? I think I need a subselect but how?

Answer 1

Try this:

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

UPDATE I'm sorry, I don't read that you want it all in the same query. For this reason the answer of greg it's better. However, other possibility if you have a postgresql version >= 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

I would use the ROLLUP function on POSTRGESQL:

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

This will give you a grand total of any value that you aggregate and can also be used for aggregating multiple columns.

Hope it helps!

Answer 3

Well this should help you:

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

Answer 4

If you want all 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 it helps…