Help with counting Query

I am trying to find a query to count rows with multiple conditions.
I have files which are suffixed with -R and then a number from 1 to 3 and
then two digits 00-14.
the field is char
i.e.:
-R101
-R102
-R114
-R201
-R302
I need to determine how many 101, how many 102, etc through 114 and then the
same for
the 201-214 series and then
the 301-314 series.
I will be sending the query from Visual Basic using ADODC so I am not sure
how the data will be returned. If it was written to a temp table that would
be great.
Thanks,
Bob Hiller
Lifts for the Disabled LLCselect right(columnName, 3), count(columnName)
from tableName
group by right(columnName, 3)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>I am trying to find a query to count rows with multiple conditions.
> I have files which are suffixed with -R and then a number from 1 to 3 and
> then two digits 00-14.
> the field is char
> i.e.:
> -R101
> -R102
> -R114
> -R201
> -R302
> I need to determine how many 101, how many 102, etc through 114 and then
> the same for
> the 201-214 series and then
> the 301-314 series.
> I will be sending the query from Visual Basic using ADODC so I am not sure
> how the data will be returned. If it was written to a temp table that
> would be great.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
>|||Thank you,
That worked great but I did not ask the full question. Maybe you can help
again.
if these strings are in a column
12345678-R101
12345678-R201
12345678-R301
98564512-R112
18752381-R101
18752381-R201
18752381-R110
18752381-R111
18752381-R211
If there is a -R2 there will always be a -R1. Likewise if there is a -R3
there will always be a -R2.
In the above example I need to return
12345678-R301
98564512-R112
18752381-R201
18752381-R110
18752381-R211
I hope I have explained this well enough.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
> select right(columnName, 3), count(columnName)
> from tableName
> group by right(columnName, 3)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>|||select left(columnName, charindex('-R',columnName)+1),
MAX(substring(columnName,charindex('-R',columnName)+2,10))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Thank you,
> That worked great but I did not ask the full question. Maybe you can help
> again.
> if these strings are in a column
> 12345678-R101
> 12345678-R201
> 12345678-R301
> 98564512-R112
> 18752381-R101
> 18752381-R201
> 18752381-R110
> 18752381-R111
> 18752381-R211
> If there is a -R2 there will always be a -R1. Likewise if there is a -R3
> there will always be a -R2.
> In the above example I need to return
> 12345678-R301
> 98564512-R112
> 18752381-R201
> 18752381-R110
> 18752381-R211
> I hope I have explained this well enough.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
>|||OOPS... You need to convert the count values to int...
select left(columnName, charindex('-R',columnName)+1),
MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(substring(columnName,charindex('-R',columnName)+2,10))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||I will do some more checking but thus far your very appreciated suggestion
is producing some very strange results.
For one thing it is returning 2 expressions. I would expect only one.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I can live with the 2 returned expressions, they will work fine.
Here is what is returned when I run the sample:
12345678-R301
98564512-R112
18752381-R211
I am missing:
18752381-R201
18752381-R110
Think of the first number after the -R as a counter for the last 2 numbers
that represent a group.
When these are in the table
18752381-R101 01 is the group and 1 is the counter
18752381-R201 01 is the group and 2 is the counter
we want to return the largest counter for group 01 for the number to the
left of -R
return (18752381-R201)
18752381-R110 10 is the group and 1 is the counter
return (18752381-R110) it is the only group 10 for the number to the left
of -R
18752381-R111 11 is the group and 1 is the counter
18752381-R211 11 is the group and 2 is the counter
return (18752381-R211)
Thank in advance,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||select max(a)
from(select '12345678-R101'
union all select '12345678-R201'
union all select '12345678-R301'
union all select '98564512-R112'
union all select '18752381-R101'
union all select '18752381-R201'
union all select '18752381-R110'
union all select '18752381-R111'
union all select '18752381-R211')x(a)
group by left(a,8),right(a,2)
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Ok, I can live with the 2 returned expressions, they will work fine.
> Here is what is returned when I run the sample:
> 12345678-R301
> 98564512-R112
> 18752381-R211
> I am missing:
> 18752381-R201
> 18752381-R110
> Think of the first number after the -R as a counter for the last 2 numbers
> that represent a group.
> When these are in the table
> 18752381-R101 01 is the group and 1 is the counter
> 18752381-R201 01 is the group and 2 is the counter
> we want to return the largest counter for group 01 for the number to the
> left of -R
> return (18752381-R201)
> 18752381-R110 10 is the group and 1 is the counter
> return (18752381-R110) it is the only group 10 for the number to the
> left of -R
> 18752381-R111 11 is the group and 1 is the counter
> 18752381-R211 11 is the group and 2 is the counter
> return (18752381-R211)
> Thank in advance,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
>|||oj,
Thanks for the suggestion but the values where just given as samples. There
are thousands of rows that I have to search through. I don't think this
approach will work.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"oj" <nospam_ojngo@.home.com> wrote in message
news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
> select max(a)
> from(select '12345678-R101'
> union all select '12345678-R201'
> union all select '12345678-R301'
> union all select '98564512-R112'
> union all select '18752381-R101'
> union all select '18752381-R201'
> union all select '18752381-R110'
> union all select '18752381-R111'
> union all select '18752381-R211')x(a)
> group by left(a,8),right(a,2)
>
> --
> -oj
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
>|||Bob,
You need to adapt the technique to your data.
e.g.
select max(your_col)
from tb
group by left(your_col,8),right(your_col,2)
If it does not give you the desired result, you'd want to post ddl + sample
data + expected result here so we can help.
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uRMPBI8TGHA.5836@.TK2MSFTNGP10.phx.gbl...
> oj,
> Thanks for the suggestion but the values where just given as samples.
> There are thousands of rows that I have to search through. I don't think
> this approach will work.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
>