asked on

SQL count distinct

Hi,

I have a table that has fields in it like so

HA7 5PQ
HA8 6ZA
WA19 7ZE
WA20 4AZ
HA5 5AE
HA8 6RT
WA19 7ZA

and so on

I want to do a query to count all distinct postcodes excluding the last 3 digits so for the example above would look like this

HA7
HA8
WA19
WA20
HA5

Which means there are 5 distinct data

The table that it is stored in is postcode and the column is called codes

please help

Thanks,

R8VI

ASKER CERTIFIED SOLUTION

James Murrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mimran18

Select Left([Field Name],3) from [Table Name] Group By Left([Field name],3)

Pratima

select distinct substring(postcode, 1, (len(postcode) - 3)) from codes

Bhavesh Shah

Hi,

I think you wanted to separate data based on " " space.

if so, check out this code.

declare @str varchar(20)
set @str = 'ADDSA7 5PDAQ'


SELECT substring(@str,1,charindex(' ',@str)-1)

Open in new window

- bhavesh

deighton

select count(distinct RTRIM(LEFT(RTRIM(codes ),LEN(RTRIM(codes )) - 3))) from postcode WHERE LEN(RTRIM(codes )) > 3;

mansooralia_yahoo

Hi,

I think bhavesh is on right track, but need to add distinct, just like

declare @str varchar(20)
set @str = 'ADDSA7 5PDAQ'


SELECT distinct substring(@str,1,charindex(' ',@str)-1)

Open in new window

Lowfatspread

;with cte as
( select left(ltrim(codes)+' ',charindex(' ',ltrim(codes)+' ') -1) as OutwardPostcode
from postcodes)
select outwardpostcode,count(*)
from cte
group by outwardpostcode
order by 1

or

;with cte as
( select left(ltrim(codes)+' ',charindex(' ',ltrim(codes)+' ') -1) as OutwardPostcode
from postcodes)
select count(distinct outwardpostcode)
from cte

ps you should really store the postcode as its component parts at least the outward and inward even if you don't go down to the sector and walk layers... you can always have a computed column on the table or view containing the whole string