Skip the SELECT *. Right now you're doing all this string manipulation and grouping in CF and AFTER you've gotten the entire record set. You could do all of that directly in the database and only have the smaller number of records returned that contain the unique list of zip codes and their counts.
Wouldn't this do what you need directly in SQL? SELECT LEFT( ZIP, 5 ), COUNT( LEFT( ZIP, 5 ) ) AS theCount FROM table -- Adrian ---------------------------------------- From: Liam O'Brien <[EMAIL PROTECTED]> Sent: Tuesday, June 10, 2008 3:57 PM To: SQL <[email protected]> Subject: Query of Queries problems I have revised my queries from before. The goal is still the same, to count up all instances of the same zip codes. The problem is the zip codes are either stored in the 9 digit format, or in the 5 digit format. I try to account for this by cutting all zip codes to 5 digits, and then counting them up, but with not luck. Here is the information: ZIP is stored as an nvarchar SELECT * FROM table SELECT ZIP, LEFT(ZIP, 5) AS shortZIP, COUNT(LEFT(ZIP,5)) AS ZIPCount FROM qGetInfo GROUP BY shortZIP I realize that the second query is now exclusively being run using coldfusion, and I found a livedoc site (http://livedocs.adobe.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm) for coldfusion mx that lists the LEFT(), COUNT() functions and the AS keyword for aliasing as valid, except I am using coldfusion 8. I did not think that it would make a difference, but I get the following error: Encountered "LEFT. Incorrect Select List, Incorrect select column, Any help would be fantastic. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3092 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
