sql - comparing substrings instead of entire string

select substring(datapath,1,5), COUNT(substring(datapath,1开发者_Go百科,5)) from batchinfo
where datapath like '%thc%'
group by datapath
having COUNT(substring(datapath,1,5))>1

i am trying to count how many of each substrings there are in the table and for some reason it counts the ENTIRE string. what am i doing wrong?

You simply need to GROUP BY the substring you're trying to count instead of the full datapath. There's also no need to repeat the substring function on the count.

select substring(datapath,1,5), COUNT(*) 
    from batchinfo
    where datapath like '%thc%'
    group by substring(datapath,1,5)
    having COUNT(*)>1

Try:

select d, count(*)
from
(
    select substring(datapath,1,5) d from batchinfo
    where datapath like '%thc%'
)
group by d
having COUNT(*) > 1