On 10/31/2011 5:05 PM, David Johnston wrote:
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bill Thoen
Sent: Monday, October 31, 2011 6:51 PM
To: Postgrresql
Subject: [GENERAL] Need Help With a A Simple Query That's Not So Simple
[...]
What I'd like to know is,
which Farms and how many are growing only corn, which and how many are
growing soybeans and which and how many are growing both?
[...]
Is there a better way to
get the farm counts or data by categories like farms growing only corn,
farms growing only soybeans, farms growing both? I'm also interested in
possibly expanding to a general case where I could select more than two
crops. and get counts of the permutations.
[...]
---------------------------------------------------------------
General Idea:
WITH crop_one AS (SELECT farm_id, crop_cd AS crop_one_cd ...
), crop_two AS (SELECT farm_id, crop_cd AS crop_two_cd
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
Records with NULL for "crop_one_cd" only grow crop 2, records with NULL for
"crop_two_cd" only grow crop 1, records where neither field is NULL grow
both.
Not sure regarding the general case. You likely want to use ARRAY_AGG to
get a result like:
Farm_id_100, { 'CROP_CD_1', 'CROP_CD_2' }
You could then probably get a query to output something like:(crop_id, farms_exclusive, farms_shared, farms_without)
Where each of the "farms_" columns is an array of farm_ids that match the
particular conditional
= ALL (exclusive); != ALL && = ANY (shared); != ANY (without)
David J.
Thanks David! That worked great! When I filled in the the query from the "general idea" in your example above like so:
WITH crop_one AS (
SELECT farm_id, crop_cd AS corn FROM gfc_inilmoidia_2007 WHERE crop_cd ='0041'
), crop_two AS (
SELECT farm_id, crop_cd AS soybeans FROM gfc_inilmoidia_2007 WHERE crop_cd = '0081'
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
It produced the following (which is essentially the base of what I'm looking for):
farm_id | corn | soybeans
---------+------+----------
1473 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1475 | 0041 |
1475 | 0041 |
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1477 | 0041 |
1478 | 0041 | 0081
1479 | 0041 |
1480 | | 0081
1480 | | 0081
Thanks so much for the quick reply. You've also just opened up a whole new area of query possibilities for me of which I wasn't aware
- Bill Thoen
