Count 5 digit zip codes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count 5 digit zip codes

Author

Topic

Dale45039
Starting Member

41 Posts

Posted - 2012-06-19 : 10:14:41

My zip code table contains 5 digit and 9 digit zip codes. I want to make all zip codes 5 digit and count them. Thanks for your help in advance!

Here is what I tried...
SELECT LEFT (vwGenCustInfo.Customer_Zip_Code, 5), vwGenCustInfo.Customer_Zip_Code, Count(vwGenCustInfo.Customer_Zip_Code) AS 'Total'
FROM Database.DBS.vwGenCustApptInfo vwGenCustApptInfo, Database.DBS.vwGenCustInfo vwGenCustInfo
WHERE vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID AND ((vwGenCustApptInfo.Appt_Sched_Location_Abbr='OFCHARLE'))
GROUP BY vwGenCustInfo.Customer_City, vwGenCustInfo.Customer_Zip_Code
ORDER BY 'Total' DESC

Here are my results:
COLUMN1 Customer_Zip_Code Total
40228 40228 37
47130 471308050 35
47119 47119 30
47104 47104 26
47203 47203 26
47111 471111047 21
47172 471728933 19
47112 47112 19
47145 47145 17
47130 47130 15

Notice there are two entries for 47130 in Column1. I would like only one entry for each five digit zip code (EG: 47130 total should be 35+15=50).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-19 : 10:49:34

Change it to this:

SELECT
	LEFT (vwGenCustInfo.Customer_Zip_Code, 5),
	MAX(vwGenCustInfo.Customer_Zip_Code) AS OneOfTheNineDigitZipCodes,
	COUNT(vwGenCustInfo.Customer_Zip_Code) AS 'Total'
FROM
	db.DBS.vwGenCustApptInfo vwGenCustApptInfo,
	db.DBS.vwGenCustInfo vwGenCustInfo
WHERE
	vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
	AND ((vwGenCustApptInfo.Appt_Sched_Location_Abbr = 'OFCHARLE'))
GROUP BY
	vwGenCustInfo.Customer_City,
	LEFT (vwGenCustInfo.Customer_Zip_Code, 5)
ORDER BY
	'Total' DESC

Dale45039
Starting Member

41 Posts

Posted - 2012-06-19 : 13:54:36

It worked beautifully!! Thank you sunitabeck

Dale45039
Starting Member

41 Posts

Posted - 2012-06-20 : 13:26:35

I just realized that the solution is GROUPing BY Customer_City. When the Customer_City is missspelled, I get a separate TOTAL for each misspelled city.

Can we GROUP BY "LEFT (vwGenCustInfo.Customer_Zip_Code, 5)" only but still see the city name in the results?

If that is not possible, how can I get TOTALs without the city name in the results?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-20 : 14:01:56

quote:
Originally posted by Dale45039

I just realized that the solution is GROUPing BY Customer_City. When the Customer_City is missspelled, I get a separate TOTAL for each misspelled city.

Can we GROUP BY "LEFT (vwGenCustInfo.Customer_Zip_Code, 5)" only but still see the city name in the results?

If that is not possible, how can I get TOTALs without the city name in the results?

Any column that you want to include in the select list must either be inside an aggregate function, or must be included in the group by clause. So, you could do this, for example:

SELECT
	LEFT (vwGenCustInfo.Customer_Zip_Code, 5),
	MAX(vwGenCustInfo.Customer_City) AS Customer_City,
	MAX(vwGenCustInfo.Customer_Zip_Code) AS OneOfTheNineDigitZipCodes,
	COUNT(vwGenCustInfo.Customer_Zip_Code) AS 'Total'
FROM
	db.DBS.vwGenCustApptInfo vwGenCustApptInfo,
	db.DBS.vwGenCustInfo vwGenCustInfo
WHERE
	vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
	AND ((vwGenCustApptInfo.Appt_Sched_Location_Abbr = 'OFCHARLE'))
GROUP BY
	LEFT (vwGenCustInfo.Customer_Zip_Code, 5)
ORDER BY
	'Total' DESC

Of course, this does not guarantee that you will get the correct spelling of the customer city. (And, you don't have to use MAX function, any appropriate aggregate function that can be applied to the data type would be syntactically acceptable)

Dale45039
Starting Member

41 Posts

Posted - 2012-06-20 : 15:20:11

sunitabeck, you're my hero!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-20 : 15:48:06

Why, thank you!!

You should, however, be aware of a possible flaw in the way I have written it, which Visakh has pointed out here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175954

You may or may not care about that - if you do, there are alternate approaches that can circumvent that. If you don't care about this, I like the simplicity of this.

Dale45039
Starting Member

41 Posts

Posted - 2012-07-06 : 08:25:57

"Customer_City" is a free text field in our system and we have some cities misspelled and others that do not match the correct "Customer_Zip_Code".

How can I use a spreadsheet, csv, or any other static or pre-populated source (with "XLS_City" and "XLS_Zip" columns) as a reference for this query?

I looking for a way take the "Customer_Zip_Code" find a match in "XLS_Zip" and populate the "Customer_City" column with the correctly spelled/matched city name from "XLS_City" - make sense or clear as mud?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 09:36:06

One approach would be to import your reference data on cities and zip codes from the excel sheet into database tables. Once you have that, you can join to that table. Then depending on what you need, if a match is not found in the list of Cities, you can flag it etc. For example, like this - where I am using your previous query as a subquery

SELECT
	s.*,
	CASE WHEN r.City IS NULL THEN 'Invalid' ELSE '' END AS IsCityValid
FROM
(
	SELECT
		LEFT (vwGenCustInfo.Customer_Zip_Code, 5),
		MAX(vwGenCustInfo.Customer_City) AS Customer_City,
		MAX(vwGenCustInfo.Customer_Zip_Code) AS OneOfTheNineDigitZipCodes,
		COUNT(vwGenCustInfo.Customer_Zip_Code) AS 'Total'
	FROM
		db.DBS.vwGenCustApptInfo vwGenCustApptInfo,
		db.DBS.vwGenCustInfo vwGenCustInfo
	WHERE
		vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
		AND ((vwGenCustApptInfo.Appt_Sched_Location_Abbr = 'OFCHARLE'))
	GROUP BY
		LEFT (vwGenCustInfo.Customer_Zip_Code, 5)
)s
LEFT JOIN CityReferenceTable r ON
	r.City = s.CustomerCity

Dale45039
Starting Member

41 Posts

Posted - 2012-07-06 : 10:32:22

"import your reference data on cities and zip codes from the excel sheet into database tables" scares me - will that affect the source database ("Database.DBS")?

If it is safe and only affects the query, how do I import?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 11:05:57

It would "affect" the database in the sense that you would be creating a new table and storing data into that table. But, it should not/will not interact with other tables or queries (except in terms of resources etc., which is unlikely to be an issue if you have a small table with one column of city names).

Dale45039
Starting Member

41 Posts

Posted - 2012-07-06 : 15:39:23

OK, how do I import?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-07 : 08:42:15

[code]Will there be more than one city with the same name? If that is a possibility, we will n eed to do something more to associate the correct city with the correct rows in your query. Assuming there aren't any dups, create a table using this:[code]create table dbo.CityNames
( City VARCHAR(256) NOT NULL PRIMARY KEY CLUSTERED);[/code]

Now import the city names into this table. If you already have the data in an Excel file, you can use the import export wizard. (In SSMS, in the object explorer, right click on the database name, then Tasks -> Import Data and follow the instructions in the wizard).

Alternatively, if you have only a few cities, you can even create insert statements, copy and paste those to SSMS an execute. For example, if your city names are in column A in Excel, create a formula in corresponding cells in column b as
[code]="INSERT INTO dbo.CityNames VALUES('" &A1&"');"[/code]
You can copy and do paste-special-values to get the insert statements.

I do not see any risk in any of the steps I described above, but if you are uncomfortable doing any of these, PLEASE test it in a development environment where you cannot do any damage to your production systems. Even if you are comfortable, still test it in a development environment as a best practice (i.e., do what I say, not what I do

)
[/code]

Dale45039
Starting Member

41 Posts

Posted - 2012-07-09 : 12:51:04

There are some city names that will be duplicated in this table (multiple zips for the same city) - how do I handle that?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 13:36:17

In that case, you should create your reference table with the ZIP code as the primary key:

CREATE TABLE dbo.Cities
( 
	City VARCHAR(256) NOT NULL,
	ZipCode VARCHAR(5) NOT NULL PRIMARY KEY CLUSTERED
)

And, the query would then be:

SELECT
	s.*,
	CASE WHEN r.City <> s.Customer_City THEN 'Invalid' ELSE '' END AS IsCityValid
FROM
(
	SELECT
		LEFT (vwGenCustInfo.Customer_Zip_Code, 5) As ZipCode,
		MAX(vwGenCustInfo.Customer_City) AS Customer_City,
		MAX(vwGenCustInfo.Customer_Zip_Code) AS OneOfTheNineDigitZipCodes,
		COUNT(vwGenCustInfo.Customer_Zip_Code) AS 'Total'
	FROM
		db.DBS.vwGenCustApptInfo vwGenCustApptInfo,
		db.DBS.vwGenCustInfo vwGenCustInfo
	WHERE
		vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
		AND ((vwGenCustApptInfo.Appt_Sched_Location_Abbr = 'OFCHARLE'))
	GROUP BY
		LEFT (vwGenCustInfo.Customer_Zip_Code, 5)
)s
LEFT JOIN Cities r ON
	r.ZipCode = s.ZipCode ;

Dale45039
Starting Member

41 Posts

Posted - 2012-07-09 : 16:46:23

quote:
Originally posted by Dale45039

There are some city names that will be duplicated in this table (multiple zips for the same city) - how do I handle that?

I mean, how do I create a table where there are dups?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 18:09:34

quote:
Originally posted by Dale45039

quote:
Originally posted by Dale45039

There are some city names that will be duplicated in this table (multiple zips for the same city) - how do I handle that?

I mean, how do I create a table where there are dups?

You would use the create table statement that I had posted at 07/09/2012 : 13:36:17. Then you can use either import/export wizard (that I had described in an earlier post) to import an excel file that has two columns - City name and Zip code - into the database.

If your only goal is to check if city names are valid, then you can still use the original query that I had suggested where you have only one column for the City, but import only distinct cities into that table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources