Re: CROSSTAB( .. only one column has values... )

Adam Tauno Williams schrieb am 05.01.2021 um 16:46:
> I'm using the crosstab feature and do not understand why I am only
> getting values in the first column.
>
> The query:
> SELECT
>    date_trunc('month', t2.value_date) AS invoice_date,
>    t1.value_string AS invoice_type
>    COUNT(*)
> FROM document d
>    LEFT OUTER JOIN obj_property t1
>      ON (t1.obj_id = d.document_id
>          AND t1.namespace_prefix = 'http://www.example.com/ctabs'
>          AND t1.value_key = 'invoiceType')
>    LEFT OUTER JOIN obj_property t2
>      ON (t2.obj_id = d.document_id
>          AND t2.namespace_prefix = 'http://www.example.com/ctabs'
>          AND t2.value_key = 'invoiceDate')
> WHERE ((d.project_id = 1134510213) AND (d.is_folder = 0))
> GROUP BY 1,2
>
>   - has results like -
>
> invoice_date invoice_type count
> ------------ ------------ -----
> 2013-02      service      3454
> 2013-03      service      3512
> 2013-03      parts        5366
> 2013-04      parts        5657
> 2013-04      service      4612
> 2013-05      service      4946
> 2013-05      parts        5508
> ...
>
> So I put this in as a crosstab:
>

I find using filtered aggregation to be way easier and more flexible than using crosstab():

     SELECT date_trunc('month', t2.value_date) AS invoice_date,
            count(*) filter (where value_string = 'rental') as rental,
            count(*) filter (where value_string = 'sales') as sales,
            count(*) filter (where value_string = 'service') as service
     FROM document d
        LEFT OUTER JOIN obj_property t1
          ON (t1.obj_id = d.document_id
              AND t1.namespace_prefix = 'http://www.example.com/ctabs'
              AND t1.value_key = 'invoiceType')
        LEFT OUTER JOIN obj_property t2
          ON (t2.obj_id = d.document_id
              AND t2.namespace_prefix = 'http://www.example.com/ctabs'
              AND t2.value_key = 'invoiceDate')
     WHERE ((d.project_id = 1134510213) AND (d.is_folder = 0))
     GROUP BY 1


Thomas