group by

  • Thread starter Thread starter Piedro
  • Start date Start date
P

Piedro

Hi everyone,

I've got a query:
select distinct(substring(beginx,1,5))as beginx
from tc201
where
lijn is null and isnumeric(substring(vak,1,1)) = 1
or
lijn = 'nieuw' and isnumeric(substring(vak,1,1)) = 1

and from this query I get:

beginx
[X00]
[X01]
[X02]
[X10]
[X11]
[X12]
[X13]

this result is ok
the [ ] are part of the field.

But now I want the result to look like:

beginx records
X0 3
X1 4

So I'm using the same query as above but with a group by and count:

select distinct(substring(beginx,2,2)) as beginx ,
count(substring(beginx,2,2)) as records
from tc201
where
lijn is null and isnumeric(substring(vak,1,1)) = 1
or
lijn = 'nieuw' and isnumeric(substring(vak,1,1)) = 1
group by substring(beginx,2,2)

but I'm not getting what I want, I get:
beginx records
X0 8
X1 12

and not:
beginx records
X0 3
X1 4

what am I doing wrong on this one?
thnx for any help

Peter
 
Back
Top