From: | Adam Tauno Williams <awilliam(at)whitemice(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | CROSSTAB( .. only one column has values... ) |
Date: | 2021-01-05 15:46:08 |
Message-ID: | 39d8f6180ae91806f6d6738ccb761b4b615e404c.camel@whitemice.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
SELECT * FROM crosstab(
$$
SELECT
SUBSTR(t2.value_string, 1, 7) 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
$$) AS final_result (invoice_date VARCHAR, "parts" BIGINT, "rental"
BIGINT, "sales" BIGINT, "service" BIGINT);
- and I get the results of -
invoice_date parts rental sales service
------------ ----- ------ ------ -------
2001-09 1 (null) (null) (null)
2007-07 1 (null) (null) (null)
2013-02 5353 (null) (null) (null)
2013-02 3454 (null) (null) (null)
2013-03 3512 (null) (null) (null)
2013-03 5366 (null) (null) (null)
...
Only the first column has values, all the rest are NULL.
I assume I am missing something stupid.
--
Adam Tauno Williams <mailto:awilliam(at)whitemice(dot)org> GPG D95ED383
Systems Administrator, Python Developer, LPI / NCLA
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-01-05 16:12:47 | Re: CROSSTAB( .. only one column has values... ) |
Previous Message | Andrus | 2021-01-05 14:49:06 | duplicate key value violates unique constraint pg_default_acl_role_nsp_obj_index |