group by

[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

[Armin Zingler]

"Piedro" <pp*****@hotmail.com> schrieb

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

This is not a VB.Net related question. Please post to the following group:
microsoft.public.dotnet.framework.adonet
--
Armin

How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html