CODEWITHSUNDEEP
postgresqlpivotcrosstab
Muhammad Rafi Bahrur Rizki
Muhammad Rafi Bahrur Rizki

Reputation: 451

Pivot / Crosstab PostgreSQL ERROR: invalid return type

Hello I have created a view, but I want to pivot it with dynamic years.

Output before pivoting:

1

Expected output:

2

My query :

SELECT *
FROM crosstab(
'   select b.jenisiuran,
    date_part(''year''::text, a.insertdate) AS tahun,
    sum(b.jumlah_amt) AS jumlah
    FROM blm_dpembayaraniuran a
    JOIN blm_dpembayaraniuranline b ON a.blm_dpembayaraniuran_key::text = b.blm_dpembayaraniuran_key::text
    GROUP BY date_part(''year''::text, a.insertdate), b.jenisiuran'  
) AS (TRANSAKSI TEXT, "2019" NUMERIC, "2020" NUMERIC, "2021" numeric);

and I'm getting error like this :

ERROR: invalid return type
Detail: SQL rowid datatype does not match return rowid datatype.

Thanks for helping me

Upvotes: 0

Views: 701

Answers (1)

user330315
user330315

Reputation:

I find using filtered aggregation easier to work with than crosstab()

select b.jenisiuran as transaksi,
       sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2019) as "2019",
       sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2020) as "2020",
       sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2021) as "2021",
       sum(b.jumlah_amt) as total
FROM blm_dpembayaraniuran a
  JOIN blm_dpembayaraniuranline b ON a.blm_dpembayaraniuran_key::text = b.blm_dpembayaraniuran_key::text
WHERE a.insertdate >= date '2019-01-01' 
  AND a.insertdate <  date '2022-01-01'
GROUP b.jenisiuran;

Adding a range condition on inserdate should improve performance as the grouping only needs to be done for the rows in the desired range, not on all rows in the both tables.

Upvotes: 1

Related Questions