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

[Thomas Kellerer]

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