?
Solved

Search for top post codes

Posted on 2005-05-17
3
Medium Priority
?
192 Views
Last Modified: 2010-03-19
ok, I have a nvarchar(10) field to store a UK postcode, such as DK3 7VV, or LL13 5GJ

What Select quert do I need to group all postcodes by their areas, i.e. DK3  or LL13?

Say I have a data set of:

DK3 5HH
DL2 1HK
DK3 7JS
LL13 5HK
LL13 6SD
DK3 6AS


It would return

Area        Count
DK3         3
LL13        2
DL2         1



I have this code at the moment:

select COUNT(uniquekey) AS COUNT, left(addresspostcode,4) As AddressPostCode
from campaign
where isthisalead=1
GROUP BY addresspostcode

but it's looking at the postcode before it is truncated, so it evaluating the full string before it groups it...I want to truncate it then group it....

does this make sense?

TIA
0
Comment
Question by:XanderP
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
ID: 14017582
Hi,

Give this a try:

SELECT RTRIM(LEFT(addresspostcode,4)) AS AddressPostCode, COUNT(1)
FROM Campaign
WHERE isthisalead = 1
GROUP BY RTRIM(LEFT(addresspostcode,4))

HTH
0
 
LVL 3

Author Comment

by:XanderP
ID: 14017631
Looks spot on adathelad :)


I've added a order by:


SELECT RTRIM(LEFT(addresspostcode,4)) AS AddressPostCode, COUNT(1) as Count
FROM Campaign
WHERE isthisalead = 1
GROUP BY RTRIM(LEFT(addresspostcode,4))
order by Count desc



Thanks :)
0
 
LVL 23

Expert Comment

by:adathelad
ID: 14017655
no problem :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question