Count Distince Issue with Postcodes

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 2005 Forums

Transact-SQL (2005)

Count Distince Issue with Postcodes

Author

Topic

mickyjtwin
Starting Member

12 Posts

Posted - 2007-04-10 : 22:10:15

I have 2 tables, one contains a list of all postcodes and their suburbs, the other table contains a list of members, and their postcode address. In the full postcode list, there might be 4 suburbs to a postcode i.e.

tblPostcodes
Postcode Suburb Latitude Longitude
5555 Burb1 44.4444 -39.3933
5555 Burb2 44.4444 -39.3911
5555 Burb3 44.4444 -39.1002
3958 Another Burb 29.2999 -29.2999

tblMembers
Postcode
Member1 5555
Member2 3958
Member3 5555

I need a query which will return the postcode, the count, the first suburb name found(or omit name and return the first lat, long found) and the latitude and longitude, i.e.
Postocde Count Suburb Latitude Longitude
5555 2 Burb1 44.4444 -39.3933
3958 1 Another Burb 29.2999 -29.2999

I can return the distinct postcode and count, but once I start with the suburb and lat, long, it starts returning all the postcodes

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 22:13:57

see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81359
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

mickyjtwin
Starting Member

12 Posts

Posted - 2007-04-10 : 22:17:59

This doesn't really help my situation, as it's working on distanct between point etc.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 22:42:04

Sorry read wrongly. Where is my coffee . . . .


declare @tblPostcodes table
(
	Postcode 	int,
	Suburb 		varchar(20),
	Latitude 	decimal(10,4),
	Longitude	decimal(10,4)
)

insert into @tblPostcodes
select	5555, 'Burb1', 		44.4444, -39.3933 union all
select	5555, 'Burb2', 		44.4444, -39.3911 union all
select	5555, 'Burb3', 		44.4444, -39.1002 union all
select	3958, 'Another Burb', 	29.2999, -29.2999

declare @tblMembers table
(
	Member		varchar(10),
	Postcode	int
)
insert into @tblMembers
select	'Member1', 5555 union all
select	'Member2', 3958 union all
select	'Member3', 5555

select	p.Postcode, m.Cnt, p.Suburb, p.Latitude, p.Longitude
from	@tblPostcodes p 
	inner join
	(
		select	Postcode, Suburb = min(Suburb)
		from	@tblPostcodes
		group by Postcode
	) a
	on	p.Postcode	= a.Postcode
	and	p.Suburb	= a.Suburb
	inner join
	(
		select	Postcode, Cnt = count(*)
		from	@tblMembers
		group by Postcode
	) m
	on	p.Postcode	= m.Postcode

Kristen
Test

22859 Posts

Posted - 2007-04-11 : 04:28:04

"Sorry read wrongly"

I read "Count Distince Issue with Postcodes" as Distance, rather than Distinct, too

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-11 : 04:48:32

quote:
Originally posted by Kristen

"Sorry read wrongly"

I read "Count Distince Issue with Postcodes" as Distance, rather than Distinct, too

So i am not the only one that need glasses

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-12 : 07:22:44

select postcode, Count = count(*), Min(suburb), Latitude = min(latitude), Longitude = min(longitude) from tblPostcodes where postcode in
(select postcode from tblMembers) group by postcode

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-12 : 07:59:08

sorry count is from members table right,
select p.postcode, Count = min(m.cnt), Suburb = min(p.Suburb), Latitude = min(p.Latitude), Longitude = min(p.Longitude)
from tblPostcodes p inner join
(select postcode, cnt = count(*) from tblMembers group by postcode) m on p.postcode = m.postcode
group by p.postcode

Kristen
Test

22859 Posts

Posted - 2007-04-12 : 08:41:49

You don't need the outer aggregat, do you? (Assuming that postcode is unique within the tblPostcodes table.


select p.postcode, Count = m.cnt, Suburb = p.Suburb, Latitude = p.Latitude, Longitude = p.Longitude
from tblPostcodes p inner join 
(select postcode, cnt = count(*) from tblMembers group by postcode) m on p.postcode = m.postcode

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources