2

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.

2

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:

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:

Related CTE examples:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.