pesky select query
От | Ingram, Bryan |
---|---|
Тема | pesky select query |
Дата | |
Msg-id | 01CCE949D2717845BA2E573DC081167E052F42@BKMAIL.sfsinternal.com обсуждение исходный текст |
Ответы |
Re: pesky select query
|
Список | 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
В списке pgsql-sql по дате отправления: