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:
Best Answer
The following query produced the results I require. However, if there is more than 2 parts (part of database column), then additional columns, AVG3, SUM3 need to be generated dynamically.