Count variation in the output

Question

SELECT *
FROM crosstab(
   'SELECT request_address,CASE WHEN code = $$200$$ THEN $$success$$ ELSE $$failure$$
        END AS code,id,count(*) as count from table_1 group by 2,1,3 order by 2,1,3',
    $$VALUES (''),('306'),('308'),('309'),('310'),('311'),('312'),('313'),('314'),('315'),('316'),('317'),('NULL') $$)
 AS ct(request_address text,code text,"blank" bigint,"306" bigint,"308" bigint,"309" bigint,"310" bigint,"311" bigint,"312" bigint,"313" bigint,"314" bigint,"315" bigint,"316" bigint,"317" bigint,"null" bigint)

Due to incorrect grouping or ordering the code produces incorrect count and missing values in the output. It can be rectified by changing the grouping but I want to understand how it affects the output.

How to fix grouping or ordering for this query?

Answer 1

For starters, input values must be sorted by the "row_name" (1st column).
And you cannot have null as "category" value.

This might work:

SELECT *
FROM   crosstab(
      $$
      SELECT request_address
            , CASE WHEN code = '200' THEN 'success' ELSE 'failure' END AS code
            , id
            , count(*)::int AS ct
      FROM    table_1
      GROUP   BY 1,2,3
      ORDER   BY 1,3     -- 1 has to be first!
      $$
    , $$SELECT unnest('{"",306,308,309,310,311,312,313,314,315,316,317}'::text[])$$
   ) AS ct(request_address text, code text
         , "blank" int,"306" int,"308" int,"309" int,"310" int,"311" int,"312" int,"313" int,"314" int,"315" int,"316" int,"317" int);

But it's guesswork while the table definition is unknown.

Basics:

• PostgreSQL Crosstab Query