PostgreSQL crosstab error

Question

I have this Postgres query:

SELECT i.nombre_inspectoria::varchar, p.nombre_pase::varchar, count(a.idatraso)::int from atrasos a
inner join inspectorias i on i.idinspectoria = a.idinspectoriafk
inner join tipo_pase p on p.idtipo_pase = a.idtipo_pasefk
inner join colegio c on c.idcolegio = i.idcolegiofk
inner join anio_academico an on an.idcolegiofk = c.idcolegio
where i.idcolegiofk = 1
and an.idanioacademico = 1
group by a.idinspectoriafk, i.nombre_inspectoria, p.idtipo_pase,i.idinspectoria
order by i.idinspectoria asc

And i get this output:

nombre_inspectoria | tipo_pase |  count
 inspectoria 1     |  jornada  |     5
 inspectoria 1     |  medico   |     7
 inspectoria 2     |   cambio  |     6
 inspectoria 2     |  jornada  |     4
 inspectoria 3     |  cambio   |     5
 inspectoria 4     |  medico   |     1

But i need this output:

nombre_inspectoria | pase_jornada | pase_cambio | otros 
 inspectoria 1     |      5       |     0       |   7
 inspectoria 2     |      4       |     6       |   0
 inspectoria 3     |      0       |     5       |   0
 inspectoria 4     |      0       |     0       |   5

I looked up some posts here and tried many things, right now I have:

SELECT *
FROM crosstab(
    'SELECT i.nombre_inspectoria::varchar, p.nombre_pase::varchar, count(a.idatraso)::int from atrasos a
    inner join inspectorias i on i.idinspectoria = a.idinspectoriafk
    inner join tipo_pase p on p.idtipo_pase = a.idtipo_pasefk
    inner join colegio c on c.idcolegio = i.idcolegiofk
    inner join anio_academico an on an.idcolegiofk = c.idcolegio
    where i.idcolegiofk = 1
    and an.idanioacademico = 1
    group by a.idinspectoriafk, i.nombre_inspectoria, p.idtipo_pase, i.idinspectoria
    order by i.idinspectoria asc')
AS a ("nombre_inspectoria" varchar, "pase_jornada" varchar, "pase_cambio" varchar, "otros" varchar);

But i get this error:

ERROR:  return and sql tuple descriptions are incompatible

What is wrong with the query?

score 0 · Answer 1 · answered Jul 01 '16 at 19:10

0

For one thing, you're selecting three attributes in SELECT and asking that they be displayed as a four-tuple in AS.

answered Jul 01 '16 at 19:10

score 0 · Answer 2 · answered Jul 01 '16 at 20:27

Some of the return columns must be numeric instead of varchar.

AS a ("nombre_inspectoria" varchar, "pase_jornada" numeric, "pase_cambio" numeric, "otros" numeric);

But honestly I don't think this would turn into your desired result since you have missing values (0's) and according to tablefunc documentation you need to use the two-parameter form...

The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

You need a second select to get all distinct values for resulting column tipo_pase. This will be your category query. Something like...

SELECT DISTINCT tipo_pase_name FROM tipo_pase ORDER BY 1;

Also take into account the ordering thing. Your main query must be ordered by nombre_inspectoria and tipo_pase and your category query must be ordered by tipo_pase.
Your return columns must be in the same ordering of the category query to get the right data.

And finnaly if you have more than one value that would fit into otros you should adjust your main query and category query to transform them into a common value and sum up. CASE WHEN would be a good idea for this.

Thank so much for the reply. Put the second parameter query worked fine for me. I did must ordered my main query and my categories query too as you said. Thank YOu — Jhon Masco, Jul 02 '16 at 21:16

score 0 · Answer 3 · edited May 23 '17 at 12:32

Since your values are type integer (count(a.idatraso)::int), the 3 resulting value columns must have a matching integer type. Not ~~varchar~~, and not ~~numeric~~. So (note the bold emphasis):

SELECT *
FROM   crosstab(
    'SELECT i.nombre_inspectoria::varchar, p.nombre_pase::varchar, count(a.idatraso)::int
     FROM   atrasos a
     JOIN   inspectorias   i  on i.idinspectoria = a.idinspectoriafk
     JOIN   tipo_pase      p  on p.idtipo_pase = a.idtipo_pasefk
     JOIN   colegio        c  on c.idcolegio = i.idcolegiofk
     JOIN   anio_academico an on an.idcolegiofk = c.idcolegio
     WHERE  i.idcolegiofk = 1
     AND    an.idanioacademico = 1
     GROUP  BY a.idinspectoriafk, i.nombre_inspectoria, p.idtipo_pase, i.idinspectoria
     ORDER  BY i.idinspectoria')
AS a (nombre_inspectoria varchar, pase_jornada int, pase_cambio int, otros int);

If any of the values can be missing in the underlying tables, you'll want to use the safe form of crosstab() with two parameters. But that is not the case for the data you display. 0 is not a missing value. No row would be a missing value.

PostgreSQL Crosstab Query

PostgreSQL crosstab error

3 Answers3