CODEWITHSUNDEEP
sqlpostgresqlcrosstab
Brian Dant
Brian Dant

Reputation: 4149

How do I debug this crosstab() Postgres function?

Current Table data

Table name: Application App

id  | applied_class  |  applied_date
----+-------+-------------------------------
 27 | city1          | 2013-03-11 23:47:04.167624-04
 28 | city1          | 2013-03-11 23:58:28.90088-04
 29 | city2          | 2013-03-12 00:39:05.955988-04
 30 | city3          | 2013-03-12 01:07:28.30229-04
 31 | city2          | 2013-03-12 09:46:32.778106-04
 32 | city1          | 2013-03-12 23:06:52.262773-04
 33 | city2          | 2013-03-14 14:28:40.401831-04
 34 | city3          | 2013-03-15 19:33:59.346832-04
 35 | city2          | 2013-03-16 05:51:11.354835-04

Desired Output.

It is an aggregate count of records over time, grouped by date (day) and city.

date        |  city1   |  city2   |  city3 
------------+----------+----------+--------
2013-03-11  |   2      |   0      |    0        
2013-03-12  |   3      |   2      |    1
2013-03-13  |   3      |   2      |    1
2013-03-14  |   3      |   3      |    0    
2013-03-15  |   3      |   3      |    2
2013-03-16  |   3      |   3      |    0

Current (Failing) Query

I'm trying to progress through the query step by step, and I've hit a wall. The query below returns the following error (note that these queries are all working when I run them by themselves, outside of crosstab):

DETAIL: SQL rowid datatype does not match return rowid datatype.

select *

from crosstab(
    $$select temp_table.d,  
       applied_class,  
       sum(temp_table.ct) over (order by d) 

    from   
       (
        select count(id) ct, 
               applied_class, 
               date_trunc('day', applied_date) d from application_app 
        where applied_class like '%L13' 
        group by applied_class, d 
        order by d
        ) as temp_table

    order by 1, 2$$)  -- end crosstab

as ct ("day" date, "city1" text, "city2" text, "city3" text);

Upvotes: 1

Views: 4601

Answers (2)

mejobhoot
mejobhoot

Reputation: 91

Answering to an old post since I did not find anything concrete that helped me solve a similar problem. In my query the row column is a concat of 2 varchars and the output of crosstab is also varchar. This gave the rowid error. Changing varchar to text in crosstab output removed the error.

select * from crosstab(
    'select concat(.....) '
    ) as
ct(dname text,
ct1 float, ...;

Upvotes: 2

Chris Travers
Chris Travers

Reputation: 26464

First, read the docs

It looks to me like you need to rename your date field to "row_name" and your "applied_class" to "category."

The basic question you have to ask is "how does the function know on what criteria to pivot my data?" Once you have that it is easier.

Edit: You and I are looking at two different versions of the function. The first, the one I describe above, is the one you actually used (the one with only one argument, text sql). There is a second version where you supply the crosstab criteria yourself, and in this regard category is specified as a second argument.

You need to choose one or the other way to do it.

Upvotes: 1

Related Questions