Add column with a sum total to crosstab() query in PostgreSQL 9.0

Question

Following on from my previous question:

Creating crosstab() pivot table in PostgreSQL 9.0

I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create a view or table of the base geometry-less table.

However, this still isn't much use as I need to link it to the gazetteers_and_addresses.unit_postcode table in order to assign geometries for further analysis.

I will attach the table structure for both tables and the original code that worked to create my crosstab.

CREATE OR REPLACE VIEW adult_social_care.vw_ageband AS (
 SELECT * FROM crosstab(
 'SELECT postcode_nospace_, ageband, count(ageband) as total_count
 FROM adult_social_care.activities_in_localities_asc
 GROUP BY postcode_nospace_, ageband
 ORDER BY postcode_nospace_'
 ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64'), ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$)
 AS ct("postcode" text, "18-24" numeric, "25-34" numeric,"35-44" numeric, "45-54" numeric, "55-64" numeric, "65-74" numeric, "75-84" numeric, "85-94" numeric, "95 AND OVER" numeric));

Table defintions:

activities_in_localities_asc:

CREATE TABLE adult_social_care.activities_in_localities_asc (
 ogc_fid integer NOT NULL,
 sort numeric(5,0),
 ageband character(12),
 postcode_nospace_ character(8),
 wkb_geometry geometry,
 CONSTRAINT activities_in_localities_asc_pkey PRIMARY KEY (ogc_fid)
);

unit_postcode:

CREATE TABLE gazetteers_and_addresses.unit_postcode (
 oogc_fid serial NOT NULL,
 pc_area character(10),
 postcode_nospaces text,
 wkb_geometry geometry
);

If possible too, assign another field at the end which states a sum of all the fields to give a total_count.

If this can be done then I can create dynamic views on different factors using one geometry-less table and unit_postcode.

• Erwin Brandstetter: So do you have your answer here?

Answer 1

Proper crosstab query

First of all, the presented query would not work while you still have char(n) in your table definition - like we discussed under your previous question. You would need to convert to text or varchar first.

This works with your current table definition:

SELECT * FROM crosstab(
 'SELECT postcode_nospace_::text, ageband::text, count(ageband) AS ct
 FROM adult_social_care.activities_in_localities_asc
 GROUP BY 1, 2
 ORDER BY 1'
 ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
 , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$
 )
AS t("postcode" text
 , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
 , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint);

I am also using bigint instead of numeric to save another unnecessary type conversion (count() returns bigint).

Detailed explanation:

• PostgreSQL Crosstab Query

Better table definition

But better ALTER the data type in your table to text like @dezso suggested. Or, if you want to keep the length limit, add a CHECK constraint or use varchar(n). Never use char(n).

ALTER TABLE activities_in_localities_asc
 ALTER COLUMN ageband TYPE varchar(12)
 , ALTER COLUMN postcode_nospace_ TYPE varchar(8);
 ALTER TABLE gazetteers_and_addresses.unit_postcode
 ALTER COLUMN pc_area TYPE varchar(10);

Then you don't need to cast original column values any more:

SELECT * FROM crosstab(
 'SELECT postcode_nospace_, ageband, count(ageband)
 ...

Ideally, though, ageband should be an enum or (my preference) an ID referencing a lookup table, not plain, error-prone text ... key word: normalization.

Add total count

Building on your table, but with text or varchar() columns.

Wrap your original query into a CTE and call the the result twice in a UNION query - the 2nd call adds totals per postcode:

SELECT * FROM crosstab(
 $$WITH cte AS (
 SELECT postcode_nospace_, ageband, count(ageband) AS ct
 FROM adult_social_care.activities_in_localities_asc 
 GROUP BY 1, 2
 )
 TABLE cte -- original results
 UNION ALL -- add total per postcode
 SELECT postcode_nospace_, 'total' AS ageband, sum(ct) AS ct
 FROM cte
 GROUP BY 1
 ORDER BY 1$$ -- dollar-quotes to include single quotes easily
 ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
 , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER'), ('total')$$
 )
AS t("postcode" text
 , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
 , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint, "total" bigint);

About the TABLE command:

• Is there a shortcut for SELECT * FROM in psql?

Related CTE examples:

• Combine results of two queries, where the second relies on the first
• Get n grouped categories and sum others into one