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`; 

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.

  • 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? 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. Aug 25 '12 at 17:49
  • You do of course know that bool is synonymous with tinyint btw? 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. Aug 26 '12 at 3:22
  • 2
    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. 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;
  • 2
    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.