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