How to add summary rows to income statements in postgresql

Question

Income statement table has structure:

sgroup char(30),
account char(10),
jan numeric(12,2),
feb numeric(12,2)

and has values:

SGroup   Account  Jan     Feb

Sales   311      100     200
Sales   312       20      30
..
Other   410     3333      44
Other   411      333     344
...

How convert this table to have header and subtotals for each group:

Caption       Jan     Feb

Sales
 311          100     200
 312           20      30
Sales Total   120     230

Other
 410         3333     44
 411          333    344
Other total  3666    388
...          ...     ...
Grand Total  ...     ...

Caption column should contain group header, account numbers and group total for each group. After total there should be empty row. After that that there should be next group etc. In the end there should be a "Grand Total" row containing the sum of all rows.

Using Postgres 9.1.2 in Debian.
Mono C# ASP.NET MVC application running in Debian. If it's more reasonable, this conversion can done in MVC controller also.

score 1 · Accepted Answer · edited May 23 '17 at 12:22

I would calculate sums per group in a CTE to use it three times in the main query:

WITH total AS (
   SELECT sgroup, 'Sales Total'::text AS c, sum(jan) AS j, sum(feb) AS f
   FROM   income_statement
   GROUP  BY 1
   )
(  -- parens required
SELECT caption, jan, feb
FROM  (
   SELECT 1 AS rnk, sgroup, account::text AS caption, jan, feb
   FROM   income_statement

   UNION ALL
   SELECT 0 AS rnk, sgroup, sgroup::text, NULL, NULL FROM total

   UNION ALL
   SELECT 2 AS rnk, * FROM total
   ) sub
ORDER  BY sgroup, rnk
)
UNION ALL
SELECT 'Grand Total', sum(j), sum(f) FROM total;

The extra set of parentheses is required to include ORDER BY.

You probably don't want to use the data type char(30):

Any downsides of using data type "text" for storing strings?

How to add summary rows to income statements in postgresql

1 Answers1