Select, group by 1st 3 digits

jonki

Hi,

I am trying to do a select statement to find out where my clients are from;

select district, count(*) AS cnt from client where 1 group by district order by cnt desc

This works fine but due to the fact that every entry is handtyped the groupd by isnt very efficient.

Is there a way of doing this with the first three letters of the postcode?

This would group things perfectly but only if it ignores the last few digits.

Has anyone done anything like this before or knows how to help?

Thanks a lot.

Lars_Berg

That will depend on which DBMS you are using. Standard SQL:

select select district, count(*) AS cnt from
(select substring(district from 1 for 3) as district
    from client) dt
group by district 
order by cnt desc

jonki

Would that be the same for MySQL?

Is the double select at the begining correct?

Thanks a lot for your help.

Lars_Berg

Is the double select at the begining correct?

No, that's a mishap. It should be removed.

Would that be the same for MySQL?

The query should work in 4.1

Mysql allows expressions in the group by clause so this

select substring(district from 1 for 3) as district,
   count(*) as cnt
    from client 
group by substring(district from 1 for 3)
order by cnt desc

should work in 4.0 as well.

jonki

Many many thanks