counts of groups

Question

I have a table with a text column that contains various bits of data.

For example

Hello world  
This is a piece of text  
Q1 3  
New text  
Q2 2  
Q1 2  
Q1 3  
More text

The bits of text trhat have Q1 at the start are pieces of voting data and are the bits I am interested in.

How can I write a query that gets counts of voting data only, so for example from the table data above it would return

Q1:2 counts 1  
Q1:3 counts 2  
Q2:2 counts 1

I hope that makes sense!

Answer 1

You can just do a group by count, and use a WHERE to limit the cases you want:

SELECT table.textcol, COUNT(table.textcol)
FROM table
WHERE table.textcol LIKE "Q%"
GROUP BY table.textcol
ORDER BY table.textcol

This will output

Q1 2 -> 1
Q1 3 -> 2
Q1 1-> 1

Change the LIKE to a REGEXP if you want more control (but watch the indexes, a LIKE with the % at the end can use an index, a REGEXP not).