Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I count records with queries like

SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%word%'

For each count, mysql needs to walk thoughout the table, and this is a big problem if having long table and numerous queries.

I wonder if there is a way to make all counts in one query. In this case, when mysql walks over each row, it will process all counts, and no need to scanning the entire table over and over again.

share|improve this question
    
To add to the answers which are correct, the suggested queries only scan the table once. –  James Attard Aug 25 '12 at 7:16

3 Answers 3

up vote 8 down vote accepted

To get a count for each of those you can try

SELECT
    SUM(CASE WHEN `col1` LIKE '%something%' THEN 1 ELSE 0 END) AS count1,
    SUM(CASE WHEN `col1` LIKE '%another%' THEN 1 ELSE 0 END) AS count2,
    SUM(CASE WHEN `col1` LIKE '%word%' THEN 1 ELSE 0 END) AS count3
FROM `table1`
share|improve this answer

Similar to Aaron's solutio, shorter syntax:

SELECT
    SUM(col1 LIKE '%something%') AS count1,
    SUM(col1 LIKE '%another%') AS count2,
    SUM(col1 LIKE '%word%') AS count3
FROM `table1`

The LIKE expression makes for a boolean result. TRUE is 1, FALSE is 0, so the CASE is redundant here.

share|improve this answer
    
Very neat and concise - but what happens when the counts don't fit in a TINYINT - do you get an error or does some sort of type conversion occur? –  Jack Douglas Aug 25 '12 at 12:02
    
There is no TINYINT in this story. SUM takes whatever type of number it is (even float) and produces a numeric result. You can SUM columns of TINYINT type to get values in the thousands -- that's not a problem. –  Shlomi Noach Aug 25 '12 at 17:49
    
Great answer then, +1 –  Jack Douglas Aug 25 '12 at 18:24
    
You do of course know that bool is synonymous with tinyint btw? –  Jack Douglas Aug 25 '12 at 18:25
    
Yes, my friend, I do know that. The "There is no TINYINT in this story" was perhaps not a good sentence to start with. –  Shlomi Noach Aug 26 '12 at 3:22

If I get your need right this will perhaps this will do the trick:

SELECT SUM(CASE 
  WHEN col1 LIKE '%something' THEN 1 
  WHEN col1 LIKE '%another%' THEN 1 
END) AS result
FROM table1;
share|improve this answer
1  
Looking at Aarons anwser that is probably what you are after - to get multiple counts - not the sum of all counts. –  JohnP Aug 24 '12 at 12:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.