Received: from BKMAIL.sfsinternal.com (sfs-pat.sfs-tx.com [216.52.187.112]) by hub.org (8.10.1/8.10.1) with ESMTP id e93KlPG33731 for ; Tue, 3 Oct 2000 16:47:25 -0400 (EDT) Received: by BKMAIL.sfsinternal.com with Internet Mail Service (5.5.2650.21) id ; Tue, 3 Oct 2000 15:47:45 -0500 Message-ID: <01CCE949D2717845BA2E573DC081167E052F42@BKMAIL.sfsinternal.com> From: "Ingram, Bryan" To: "'pgsql-sql@postgresql.org'" Subject: pesky select query Date: Tue, 3 Oct 2000 15:47:44 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200010/42 Hello .. I'm having trouble figuring out a good way to return my desired results from the following table ... Table = g_state_county_app app_code | varchar() not null state_code | int4 not null county_code | int4 not null region_code | varchar() The result I'm looking for, is that for each App_Code I would like the count of distinct states. So, I may have a 1000 records, but only have 4 distinct app_codes and up to 50 distinct state_codes. What I'd like to do is produce a query that will list each app_code and the count of distinct states for that app_code on one line. I don't care what the state_code is, I just need to know the number of distinct state_codes per app_code. The data may look like this: APP1, TX, 1, A1 APP1, TX, 2, A2 APP1, CO, 3, B4 APP2, IL, 1, A3 And the result I want is this: APP1, 2 # App1 is found in 2 distinct states APP2, 1 # App2 is found in 1 distinct states So far I've tried variations of select distincts with counts() and group by's on various fields. I briefly considered trying to do this with an outer join, but I'm not sure it'd work. I'm still using 6.5.x so I'd have to simulate it with a union all/subquery. For that reason I thought'd I'd ask here first to see if there is a better way. Thanks, Bryan