<div>
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 :)
</div>

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 :)

<div>
<div class="content wysiwyg-content">
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 &nbsp;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 &nbsp; &nbsp; &nbsp; &nbsp;Count 
DK3 &nbsp; &nbsp; &nbsp; &nbsp; 3 
LL13 &nbsp; &nbsp; &nbsp; &nbsp;2 
DL2 &nbsp; &nbsp; &nbsp; &nbsp; 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
</div>
</div>

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

Search for top post codes

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.