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
