PostgreSQL crosstab query with data type mismatch

Question

I am trying to run this query:

SELECT *
FROM CROSSTAB('SELECT a.marketing_product_group::VARCHAR
                    ,(a.sales_year || '' - '' || a.sales_quater)::VARCHAR AS attribute
                    ,sum(a.sales_value)::NUMERIC AS sales_value
               FROM data_Warehouse.sales_summary a
               WHERE a.customer_code = '' SCAND ''
                     AND a.invoice_date >= '' 2015 - 04 - 01 ''
               GROUP BY a.marketing_product_group
                       ,a.sales_year || '' - '' || a.sales_quater
               ORDER BY a.sales_year || '' - '' || a.sales_quater') 
AS CT(marketing_product_group VARCHAR
       ,period1 VARCHAR
       ,period2 VARCHAR
       ,period3 VARCHAR
       ,period4 VARCHAR)

I have read many items on this site and tried to correct it, but I still get the dreaded error:

return and sql tuple descriptions are incompatible

The sub query returns data as such:

marketing_product_group ;attribute; sales_value
"K3"            ; "2016-1"  ;600.0900000000000000
"CASSETTES"     ; "2016-1"  ;291.0000000000000000
"ENDO FILES"    ; "2016-1"  ;254.2500000000000000
"NEOZYME"       ;"2016-1"   ;15.5300000000000000
"PANAVIA F"     ;"2016-1"   ;47.8600000000000000
"BOUTIQUE NIGHT";"2016-2"   ;1012.5000000000000000
"ENDO MISC"     ;"2016-2"   ;20.1000000000000000
"GLOVES 17"     ;"2016-2"   ;617.2000000000000000
"DIATECH"       ;"2016-2"   ;8.1800000000000000
""              ;"2016-2"   ;47.5000000000000000
"CARESHOP 1"    ;"2016-2"   ;14.4800000000000000
"CLASSIC 1"     ;"2016-3"   ;49.5000000000000000
"GLOVES 15"     ;"2016-3"   ;70.0000000000000000
"SOFLEX"        ;"2016-4"   ;103.2000000000000000
"OMNIA"         ;"2016-4"   ;64.0000000000000000

It is probably something so simple that I have flown past it?

Thanks for the edit wingedpanther, I was trying to figure out how to do it. Chris — Chris Nash, Feb 18 '16 at 09:43

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

Since your values are cast numeric:

sum(a.sales_value)::NUMERIC AS sales_value

Your output columns need to be numeric as well:

...
AS CT(marketing_product_group VARCHAR
       ,period1 NUMERIC 
       ,period2 NUMERIC 
       ,period3 NUMERIC 
       ,period4 NUMERIC )

There are a couple of other oddities here, but since you did not provide proper information (Postgres version, table definition), I'll leave it at that.

Basics:

PostgreSQL Crosstab Query

PostgreSQL crosstab query with data type mismatch

1 Answers1