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
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.
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With