Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e93NboG88947 for ; Tue, 3 Oct 2000 19:37:50 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.0/8.11.0) with ESMTP id e93NbiB01957; Tue, 3 Oct 2000 19:37:44 -0400 (EDT) To: "Ingram, Bryan" cc: "'pgsql-sql@postgresql.org'" Subject: Re: pesky select query In-reply-to: <01CCE949D2717845BA2E573DC081167E052F42@BKMAIL.sfsinternal.com> References: <01CCE949D2717845BA2E573DC081167E052F42@BKMAIL.sfsinternal.com> Comments: In-reply-to "Ingram, Bryan" message dated "Tue, 03 Oct 2000 15:47:44 -0500" Date: Tue, 03 Oct 2000 19:37:44 -0400 Message-ID: <1954.970616264@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200010/43 "Ingram, Bryan" writes: > 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. > 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. Update to 7.0.* and do select app_code, count(distinct state_code) from tab group by app_code; regards, tom lane