Re: Need Help With a A Simple Query That's Not So Simple

On 10/31/2011 5:05 PM, David Johnston wrote:
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)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