CODEWITHSUNDEEP
mysqlsqlselectcount
Brian Powell
Brian Powell

Reputation: 3411

MySQL add count to query as column

I would like to run a query that brings back each record that meets a certain criteria, as well as give me total number of times that occurs in my table.

So if I run:

SELECT id from history where id like "%CLM%";

I get each instance of an id from my table where "CLM" is somewhere in that id. What I want in the column NEXT to that is a count of how many times that particular record was found, and I can't seem to figure out how to do that.

What I'm looking for would be something like this:

╔══════╦═══════╗
║  id  ║ count ║
╠══════╬═══════╣
║ CLM1 ║     1 ║
║ CLM2 ║     5 ║
║ CLM3 ║    13 ║
╚══════╩═══════╝

I've tried SELECT id, count(id) from history but it returns this: 

╔══════╦══════════╗
║  id  ║ count    ║
╠══════╬══════════╣
║      ║ 12258432 ║
╚══════╩══════════╝

How do I write this query?

Upvotes: 2

Views: 4582

Answers (2)

Alex
Alex

Reputation: 17289

 SELECT id, count(*)
 from history 
 where id like "%CLM%"
 GROUP BY id

Upvotes: 0

Mureinik
Mureinik

Reputation: 311348

As you saw, you can use the count function to count rows. The part you're missing, however, is breaking down this result per id, which can be done with a group by clause:

SELECT   id, COUNT(*)
FROM     history
WHERE    id LIKE '%CLM%'
GROUP BY id

Upvotes: 5

Related Questions