CODEWITHSUNDEEP
postgresqlconditional-statementspostgres-crosstab
Ramya Mahe
Ramya Mahe

Reputation: 19

Count variation in the output

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?

Upvotes: -1

Views: 56

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656566

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:

Upvotes: 0

Related Questions