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.

  • To add to the answers which are correct, the suggested queries only scan the table once. – user10471 Aug 25 '12 at 7:16

To get a count for each of those you can try

    COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1,
    COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2,
    COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3
FROM `table1`; 
| improve this answer | |

Similar to Aaron's solutio, shorter syntax:

    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.

| 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
  • 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
  • 1
    A note to anyone who stumble upon here in search of a MS SQL Server specific answer, the SUM function as mentioned here does not work the same way in MS SQL Server. In Transct-SQL SUM can be used with numeric columns only. – user1451111 Jun 10 '18 at 14:11

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

  WHEN col1 LIKE '%something' THEN 1 
  WHEN col1 LIKE '%another%' THEN 1 
END) AS result
FROM table1;
| 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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