sql - comparing substrings instead of entire string

Question

select substring(datapath,1,5), COUNT(substring(datapath,1,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?

can you provide simple example?

Unreason
– Unreason

2010-12-10 19:18:33 +00:00
Commented Dec 10, 2010 at 19:18 — Unreason
– Unreason, Commented Dec 10, 2010 at 19:18

Joe Stefanelli · Accepted Answer · 2010-12-10 19:18:29Z

4

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

answered Dec 10, 2010 at 19:18

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcus Leon · Accepted Answer · 2010-12-10 19:15:28Z

1

Try:

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

answered Dec 10, 2010 at 19:15

Marcus Leon

57k123 gold badges302 silver badges436 bronze badges

1 Comment

BlackICE Over a year ago

I like this because you're not repeating the substring

Collectives™ on Stack Overflow

sql - comparing substrings instead of entire string

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related