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!

Konerak · Accepted Answer · 2010-06-29T13:13:40.467

2

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).

edited Jun 29 '10 at 13:13

answered Jun 29 '10 at 09:39

Konerak

Thanks for this that is exactly what I need. How would I further refine this to not include a text comment that was "Q2 2 blah lblah"? – Dave Jun 29 '10 at 11:35
Well if you are always sure the format will be 'Q1 3', you can use REGEXP, as I said. `/Q[0-9] [0-9]+/` should work. – Konerak Jun 29 '10 at 12:20
This sounds interesting but I have never used REGEXP. How does that fit into the sql command? The fomat would always be Q## ## where the hashes could be single or double digit numbers. – Dave Jun 29 '10 at 12:53
`WHERE textcol REGEXP '^Q[0-9]+ [0-9]+$'` should do. Search for "regular expressions" on google or check the REGEXP link in my answer to learn more, it's really useful! – Konerak Jun 29 '10 at 13:13
You're welcome. If this post answered your question, you can accept it, so the question is resolved and future visitors know this is a valid answer. – Konerak Jun 30 '10 at 10:36

1 Answers1