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

[Bill Thoen <bthoen@xxxxxxxxxx>]

On 10/31/2011 5:05 PM, David Johnston wrote:

From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Bill Thoen
Sent: Monday, October 31, 2011 6:51 PM
To: Postgrresql
Subject:  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