Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 826
newbie_girl Avatar asked Oct 11 '16 20:10

newbie_girl


People also ask

How do you get the sum of every column in Excel?

If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

How do you find the sum of multiple columns?

Using the SUM/SUMPRODUCT Function for Multiple Columns For this type =SUM(B2:B9). Now after pressing Enter, drag this formulated cell in cell C10 and D10 to calculate the total sales for the month of February and March. Instead of SUM function, you can also use the SUMPRODUCT function to perform this calculation.

What is the formula to total a column in Excel?

To sum columns or rows at the same time, use a formula of the form: =sum(A:B) or =sum(1:2). Remember that you can also use the keyboard shortcuts CTRL + SPACE to select an entire column or SHIFT + SPACE an entire row. Then, while holding down SHIFT, use the arrow keys to select multiple rows.


1 Answers

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;
like image 129
a_horse_with_no_name Avatar answered Oct 02 '22 08:10

a_horse_with_no_name