query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

query

Author

Topic

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-03-16 : 15:33:09

I have a table test with zip char(12) as one of the fields

I get diffrent counts when i run the folliwng two queries

1.
select left(zip,5),count(*) from test where zip <>'' group by left(zip,5)

count for zip 78767 is 1134

2.
select left(zip,5),count(*) from test where zip <>''
and zip = '78767' group by left(zip,5)
I get count of 434 which is correct

Any idea?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-16 : 15:54:09

Both are:

quote:
Originally posted by cbeganesh

I have a table test with zip char(12) as one of the fields

I get diffrent counts when i run the folliwng two queries

1.
select left(zip,5),count(*) from test where zip <>'' group by left(zip,5)

count for zip 78767 is 1134

2.
select left(zip,5),count(*) from test where zip <>''
and zip = '78767' group by left(zip,5)
I get count of 434 which is correct

Any idea?

1. groups by and displays count for "78767" (defined in the SELECT and GROUP BY as LEFT(zip,5)) which could include "78767-1000", "78767-5000","78767-9999","78767abcd"

2. counts rows where zip code = "78767" which would not include those examples

for 2nd query change WHERE zip <> '' and zip like '78767%'

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-03-16 : 16:13:45

Thanks for ur reply
But i did not understand why left(zip,5) should look for "78767-1000", "78767-5000","78767-9999","78767abcd" . It should take only the left 5 char right?

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-03-16 : 16:16:36

Also
when i remove that zip<> '' , i get the correct number in both the queries, Dont know why?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-16 : 16:45:40

Please run the code here, I hope you'll see what I'm trying to say.

SET NOCOUNT ON
go
CREATE TABLE zipcode(zip varchar(20) null)
go
INSERT zipcode(zip)
(SELECT '78767' as zip
UNION ALL
SELECT '78767-'
UNION ALL
SELECT '78767abcdef'
UNION ALL
SELECT '99999'
UNION ALL
SELECT null
UNION ALL
SELECT ''
UNION ALL
SELECT '78767'
UNION ALL
SELECT '78767'
UNION ALL
SELECT '78767-1234')

SELECT COUNT(*) FROM zipcode WHERE zip <>''
SELECT COUNT(*) FROM zipcode WHERE zip <>'' and zip = '78767'
SELECT COUNT(*) FROM zipcode WHERE zip = '78767'
SELECT COUNT(*) FROM zipcode WHERE zip like '78767%'
SELECT left(zip, 5), COUNT(*)
FROM zipcode
GROUP BY left(zip, 5)

go 

DROP TABLE zipcode
SET NOCOUNT OFF

cbeganesh
Posting Yak Master

105 Posts

Posted - 2004-03-16 : 17:04:58

Thank you very much
Now i understand the problem.
Thanks
ganesh

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-16 : 17:10:47

Cool! No problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources