CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
newbie_girl
newbie_girl

Reputation: 353

How can I get sum total of each column?

CREATE TEMPORARY TABLE

CREATE TEMP TABLE total(
gid SERIAL,
zoom smallint NOT NULL,
point integer NOT NULL,
size integer NOT NULL
);

INSERT DATA

INSERT INTO total(zoom, point, size) VALUES(9,51,21);
INSERT INTO total(zoom, point, size) VALUES(9,75,45);
INSERT INTO total(zoom, point, size) VALUES(9,74,34);
INSERT INTO total(zoom, point, size) VALUES(10,75,4);
INSERT INTO total(zoom, point, size) VALUES(10,72,63);
INSERT INTO total(zoom, point, size) VALUES(10,85,22);

COUNTING POINTS, ADDING UP SIZE based on ZOOM 

SELECT zoom,
       count(*) AS point,
       SUM(size) AS size
FROM total
GROUP BY zoom
ORDER BY zoom;

Result:

 zoom | point | size 
------+-------+------
    9 |     3 |  100
   10 |     3 |   89
(2 rows)

QUESTION

How can I return total of each column?

Wanted result:

 zoom | point | size 
------+-------+------
    9 |     3 |  100
   10 |     3 |   89
------+-------+------        
Total |     6 |  189

Upvotes: 5

Views: 9051

Answers (1)

user330315
user330315

Reputation:

The way to simulate a rollup is to simply run a second query that does the rollup. However all values in a column must have the same data type. As you want to display the label 'Total' you need to convert the number zoom from the base query to a text as well:

But as you want to sort by the actual zoom value, you also need to keep the integer value in the result.

The sort_order is necessary to make sure that rows from the first part of the union actually stay "at the top"

select zoom, point, size
FROM (
  SELECT zoom::text as zoom,
         zoom as zoom_value,
         count(*) AS point,
         SUM(size) AS size, 
         1 as sort_order
  FROM total
  GROUP BY zoom
  UNION ALL
  SELECT 'Total', 
         null,
         count(*) AS point,
         SUM(size) AS size, 
         2 as sort_order
  FROM total
) t
order by sort_order, zoom_value;

This returns: 

zoom  | point | size
------+-------+-----
9     |     3 |  100
10    |     3 |   89
Total |     6 |  189

With an up-to-date Postgres version you could do the following:

SELECT case when grouping(zoom) = 1 then 'Total' else zoom::text end,
       count(*) AS point,
       SUM(size) AS size
FROM total
GROUP BY rollup (zoom)
order by zoom;

Upvotes: 10

Related Questions