Search code examples
mysqlsqldatabasesequelpro

How to count the total number of results before a hyphen?

I am looking to count the number of results from a SQL count query, the results currently have like 50 results, but in reality there are only 5 results... the results appear in a format such as:

test1-helpme1
test1-helpme3
test1-helpme4
test2-helpme1
test2-helpme2
test3-helpme4

Is there a way I can count just the "testx-" part of the results? There can be hundreds of results so the number part of "test" can't be hardcoded

SELECT COUNT(*) as CountbyID, OriginalId FROM Table1 GROUP BY OriginalId;
Solution

  • If you want the number of the distinct occurrences of the pattern, then:

    SELECT 
  COUNT(DISTINCT LEFT(OriginalId, INSTR(OriginalId, '-') - 1)) as counter
FROM Table1

    or a counter for each one:

    SELECT 
  LEFT(OriginalId, INSTR(OriginalId, '-') - 1) pattern,
  COUNT(*) as counter
FROM Table1
GROUP BY LEFT(OriginalId, INSTR(OriginalId, '-') - 1)