Main request
It is not possible to calculate each amount separately. Do it in one SELECT and cross-tabulate result.
To keep the answer “short,” I reduced it to two columns as a result. Expand as needed.
Quick and dirty
Detach two arrays with an equal number of elements in parallel. Read more about this method here and here .
SELECT unnest('{q001001,q001002}'::text[]) AS fieldname ,unnest(ARRAY[sum(q001001), sum(q001002)]) AS result FROM stats;
"Dirty" because parallel parallel is Postgres' non-standard behavior, which some are unhappy with. However, it works like a charm. Follow the links for more details.
Detailed and clean
Use CTE and UNION ALL separate lines:
WITH cte AS ( SELECT sum(q001001) AS s1 ,sum(q001002) AS s2 FROM stats ) SELECT 'q001001'::text AS fieldname, s1 AS result FROM cte UNION ALL SELECT 'q001002'::text, s2 FROM cte;
"Clear" because it is purely standard SQL.
Minimalistic
The shortest form, but it's also harder to understand:
SELECT unnest(ARRAY[ ('q001001', sum(q001001)) ,('q001002', sum(q001002))]) FROM stats;
This works with an array of anonymous entries that are hard to ignore (but possible).
Short
To get individual columns with source types, declare the type on your system:
CREATE TYPE fld_sum AS (fld text, fldsum numeric)
You can do the same for the session temporarily by creating a temporary table:
CREATE TEMP TABLE fld_sum (fld text, fldsum numeric);
Then:
SELECT (unnest(ARRAY[ ('q001001'::text, sum(q001001)::numeric) ,('q001002'::text, sum(q001002)::numeric)]::fld_sum[])).* FROM stats;
The performance for all four options is basically the same, because the expensive part is aggregation.
SQL script demonstrating all the options (based on the script provided by @klin ).
Automation with the PL / pgSQL Function
Quick and dirty
Create and execute the code as described in the corresponding chapter above.
CREATE OR REPLACE FUNCTION f_list_of_sums1(_tbl regclass, _flds text[]) RETURNS TABLE (fieldname text, result numeric) AS $func$ BEGIN RETURN QUERY EXECUTE ( SELECT ' SELECT unnest ($1) ,unnest (ARRAY[sum(' || array_to_string(_flds, '), sum(') || ')])::numeric FROM ' || _tbl) USING _flds; END $func$ LANGUAGE plpgsql;
- Being dirty, it is also not safe for SQL injection. Use it only with a confirmed entry.
Below is the version.
Call:
SELECT * FROM f_list_of_sums1('stats', '{q001001, q001002}');
Detailed and clean
Create and execute the code as described in the corresponding chapter above.
CREATE OR REPLACE FUNCTION f_list_of_sums2(_tbl regclass, _flds text[]) RETURNS TABLE (fieldname text, result numeric) AS $func$ BEGIN -- RAISE NOTICE '%', ( -- to get debug output uncomment this line .. RETURN QUERY EXECUTE ( -- .. and comment this one SELECT 'WITH cte AS ( SELECT ' || string_agg( format('sum(%I)::numeric AS s%s', _flds[i], i) ,E'\n ,') || ' FROM ' || _tbl || ' ) ' || string_agg( format('SELECT %L, s%s FROM cte', _flds[i], i) , E'\nUNION ALL\n') FROM generate_subscripts(_flds, 1) i ); END $func$ LANGUAGE plpgsql;
Call as above.
Basic moments
SQL Fiddle showing all the options.
Beyond this: table definition
The numeric(9,0) data type is a pretty inefficient choice for defining a table. Since you do not store fractional digits and no more than 9 decimal digits, use a simple integer instead. It does the same with only 4 bytes for storage (instead of 8-12 bytes for numeric(9,0) ). If you need numerical accuracy in your calculations, you can always drop the column at a negligible cost.
In addition, I do not use varchar(n) , unless necessary. Just use text .
Therefore, I suggest:
CREATE TABLE stats ( name text ,q001001 int ,q001002 int , ... );