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.

So do you have your answer here? – Erwin Brandstetter Jan 30 '18 at 05:29 — Erwin Brandstetter, Jan 30 '18 at 05:29

score 5 · Answer 1 · edited Jun 15 '20 at 09:05

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:

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

1 Answers1

Proper crosstab query

Better table definition

Add total count

Linked