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?

Please provide the exact `CREATE TABLE table_1 ...` statement. — Erwin Brandstetter, Mar 20 '23 at 16:18
Thank you erwin. But the below code eliminates some values. Please find the table_1 attcahed — Ramya Mahe, Mar 21 '23 at 09:03
Attached table_1 below . request | code | id ---------|------|----- 27.6 | 20 | 306 27.6 | 20 | 311 27.6 | 22 | 306 27.6 | 40 | 312 27.6 | 40 | 313 27.6 | 41 | 27.6 | 41 | 314 27.6 | 43 | 315 27.6 | 43 | 316 27.6 | 42 | 306 27.62 | 20 | 306 27.62 | 20 | 310 27.62 | 20 | 311 27.62 | 20 | 312 27.62 | 41 | 27.62 | 41 | 314 27.62 | 43 | 315 27.62 | 43 | 316 27.62 | 45 | 306 27.62 | 42 | 306 — Ramya Mahe, Mar 21 '23 at 13:51
From the previous query you shared only the 1st combination of columns displays as output .For example,if 27.6 have 2 occurrences to be expected there comes only the 1st occurrence in the output — Ramya Mahe, Mar 21 '23 at 13:55

score 0 · Answer 1 · answered Mar 20 '23 at 16:32

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

Count variation in the output

1 Answers1