Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Grouping Help 2

Status
Not open for further replies.
PTmon

PTmon

IS-IT--Management
Mar 8, 2001
284
0
0
US
Hi, I'm using CF5. I have a access database with one table. That table contains rows with all of the states, cities, zip codes, and counties. I'm trying to figure the best way to list the state name, and then the number of counties and number of zipcodes in the state.

Output would look like:
State: Florida
Counties: (however many)
Zip Codes: (however many)

Output would continue for each state.

I gouped the state in my ouptut, and that part came out right, but I don't know how to group or loop through to see how many records contain the county and zip code part. Any help getting me going in the right direction would be appreciated.

 
Sort by date Sort by votes
do you have multiple records for zipcodes that overlap counties?

<cfquery name = "qstates">
Select state, county, zip
from states
order by state, county, zip
</cfquery>

<cfoutput query = "qstates" group = "state">
#qstates.state#<br>
<cfoutput group = "county">
&nbsp;&nbsp;#qstates.county#
<cfoutput>
&nbsp;&nbsp;&nbsp;&nbsp;#qstates.zip#
</cfoutput>
</cfoutput>
</cfoutput>

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
no, they dont overlap. It's just the main zipcode.

Data looks like:

ST ZIP County City
Ohio 44681 Tuscarawas Sugarcreek
Ohio 44682 Tuscarawas Tuscarawas
Ohio 44685 Stark Uniontown
etc.

Thanks,
pt


putting the "new" in "newb".....
 
Upvote 0 Downvote
I don't think I made it real clear, sorry! What I'm looking for is the number of zipcodes per state, and the number of counties per state, not a listing of them.

State: Ohio
Counties: 42
Zip Codes: 1482

State: Oklahoma
Counties: 38
Zip Codes: 1396

etc.. etc..
Thanks again.
pt


putting the "new" in "newb".....
 
Upvote 0 Downvote
oh just the query. sorry. Something like this should work. If not, i'm sure r937 our CF forum in house sql expert will whip me into shape.

SELECT state, sum(county) as countiesInState, sum(zip) as zipCodesInState FROM states GROUP BY state

<cfoutput query = "qStates">
State: #qStates.state#<br>
Counties: #qStates.countesInState#<br>
Zip Codes: #qStates.zipCodesInState#<hr>
</cfoutput>

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
bombboy, thanks for your kind words

i don't open up all threads in this forum, so i guess it's fortunate that i saw them

assuming multiple zips per county,
Code: 
select state
     , count(distinct county) as countiesInState
     , count(*) as zipCodesInState 
  from states 
group 
    by state

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)
 
Upvote 0 Downvote
why did i say sum?
that was lame...

That's what happens when you try for the early morning posts...

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
Note to self: Browse Tek-Tips AFTER the first cup of coffee. [morning]

A star for your efforts. (-:



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher
 
Upvote 0 Downvote
I hear that. Messed up another post already this morning... twice.[sadeyes]

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
I appreciate all your effors, whether before OR after coffee!

however, it's still not working :(

Code: 
<cfquery name="getzips" datasource="zip">
select ST
     , count(distinct County) as countiesInState
     , count(ZIP) as zipCodesInState
  from Z5LL
group by ST
</cfquery>
<html>
<head>
<title>Zip Codes</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<cfoutput query = "getzips">
State: #getzips.state#<br>
Counties: #getzips.countesInState#<br>
Zip Codes: #getzips.zipCodesInState#<br>
</cfoutput>
</body>
</html>

This gives me a sql error. It appears to be in the county select:

Error Diagnostic Information

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'count(distinct County)'.

SQL = "select ST , count(distinct County) as countiesInState , count(distinct ZIP) as zipCodesInState from Z5LL group by ST"

Data Source = "ZIP"

Thanks,
PT



putting the "new" in "newb".....
 
Upvote 0 Downvote
microsoft access does not support COUNT DISTINCT

you will first have to save a separate query, let's call it CountyZipsQuery --
Code: 
select ST
     , County
     , count(ZIP) as zipCodesInCounty
  from Z5LL
group 
    by ST
     , County
now you may reference this query as follows --
Code: 
<cfquery name="getzips" datasource="zip">
select ST
     , count(*) as countiesInState
     , sum(zipCodesInCounty) as zipCodesInState
  from CountyZipsQuery
group 
    by ST
</cfquery>

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)
 
Upvote 0 Downvote
Hrm.. I tried that but I'm getting an error that it can't find the first query.

Code: 
<cfquery name="CountyZipsQuery" datasource="zip">
select ST
     , County
     , count(ZIP) as zipCodesInCounty
  from Z5LL
group by ST, County
</cfquery>

<cfquery name="getzips" datasource="zip">
select ST
     , count(*) as countiesInState
     , sum(zipCodesInCounty) as zipCodesInState
  from CountyZipsQuery
group by ST
</cfquery>

Result...

ODBC Error Code = S0002 (Base table not found)

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'CountyZipsQuery'. Make sure it exists and that its name is spelled correctly.

SQL = "select ST , count(*) as countiesInState , sum(zipCodesInCounty) as zipCodesInState from CountyZipsQuery group by ST"

Data Source = "ZIP"


I copied and pasted the query name to make sure it was spelled the same.
Again, all of your efforts are appreciated.
Thanks!
pt

putting the "new" in "newb".....
 
Upvote 0 Downvote
AH! *lightbulb goes on* I tried something different in access that worked but was ugly as hell..lol.

Code: 
SELECT DISTINCT [ST], [County]
FROM Z5LL
WHERE st<>'';


SELECT DISTINCT [ST], ZIP
FROM Z5LL;


SELECT DISTINCT 
DISTINCTstateCounty.ST, Count(DISTINCTstateCounty.County)
FROM DISTINCTstateCounty group by DISTINCTstateCounty.st

UNION SELECT DISTINCT ST, Count(DISTINCTstateZIP.ZIP)  FROM DISTINCTstateZIP group by DISTINCTstateZIP.st;
[code]

I just couldn't figure how to get that translated into cf.
Thanks so much for all your help, stars for everyone!
pt


putting the "new" in "newb".....
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

LyndonOHRC
  • Locked
  • Question
Need help using ImageCrop()
Replies
1
Views
73
LyndonOHRC
LyndonOHRC
MDEJAKE
  • Locked
  • Question
autosuggest (cfc and jquery) help
Replies
0
Views
89
MDEJAKE
MDEJAKE
brettco
  • Locked
  • Question
Crosstabs help in Cold Fusion
Replies
0
Views
65
brettco
brettco
szeiss
  • Locked
  • Question
Grouping Table Problem
Replies
2
Views
60
cfSearching
cfSearching
redoakhg
  • Locked
  • Question
SQL Query Help 1
Replies
5
Views
102
redoakhg
redoakhg

Part and Inventory Search

Sponsor

Back
Top