pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pesky select query 2+ messages / 2 participants [nested] [flat]
* pesky select query @ 2000-10-03 20:47 Ingram, Bryan <BIngram@sixtyfootspider.com> 0 siblings, 1 reply; 2+ messages in thread From: Ingram, Bryan @ 2000-10-03 20:47 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pesky select query @ 2000-10-03 23:37 Tom Lane <tgl@sss.pgh.pa.us> parent: Ingram, Bryan <BIngram@sixtyfootspider.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2000-10-03 23:37 UTC (permalink / raw) To: Ingram, Bryan <BIngram@sixtyfootspider.com>; +Cc: pgsql-sql "Ingram, Bryan" <BIngram@sixtyfootspider.com> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-10-03 23:37 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-10-03 20:47 pesky select query Ingram, Bryan <BIngram@sixtyfootspider.com> 2000-10-03 23:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox