Tabulate 1

[misuser2k7]

I am trying to create a query which will tabulate the following data:

Apt Number
101
203
402
501
504
202
105
1005
103
102
201
206
403
304
205
102
101
4066
3045
205
3001
205
503
5001
206
204

As..........
Apt. Numbers with | Totals
Apt. Numbers starting with number 1 | 7
Apt. Numbers starting with number 2 | 9
Apt. Numbers starting with number 3 | 3
Apt. Numbers starting with number 4 | 3
Apt. Numbers starting with number 5 | 4

I can do this for each criterion individually ............

select count(AptNmbr) as Totals
from Apartments
where AptNmbr like '1%';

But not in the tabulated form (created above manually) through a single query.

 

[fredericofonseca]

read the manuals - you need to look at group by and substring functions to accomplish this

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[misuser2k7]

Got it!

select left(([Apt Number]),1) as 'Apt Number Starting with Number', count([Apt Number]) as GroupTotal
from Apartments
where [Apt Number] like '%'
group by left(([Apt Number]),1);

Thank you.

 

[jbenson001]

You don't need the WHERE clause in your query