CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 59491

MySQL counting number of groups

I have a rather complex statement that I'm trying to write. I'm not sure how to do it though, or even if it's possible.

I have a logging system that scans a server and catalogs every user that's online at that given moment. Here is how my table looks like:

-----------------
|    ab_logs    |
-----------------
|      id       |
|    scan_id    |
|  found_user   |
-----------------

id is an autoincrementing primary key. Has no real value other than that.

scan_id is an integer that is incremented after each successful scan of all users. It so I can separate results from different scans.

found_user. Stores which user was found online during the scan.

The above will generate a table that could look like this:

id  |  scan_id  | found_user
----------------------------
1   |     1     |   Nick
2   |     2     |   Nick
3   |     2     |   John
4   |     3     |   John

So on the first scan the system found only Nick online. On the 2nd it found both Nick and John. On the 3rd only John was still online.

I know I can get the number of users that were online during a given scan by the following query:

SELECT COUNT(*) FROM ab_logs WHERE scan_id = $scan_id

My problem is that I want to get the above for all scans in one result. Is this possible without looping the above query X times?

Upvotes: 1

Views: 229

Answers (2)

varad mayee
varad mayee

Reputation: 619

Yes you can use GROUP BY like this:

SELECT COUNT(*) FROM ab_logs GROUP BY scan_id

Upvotes: 1

BenM
BenM

Reputation: 53198

You can use the GROUP BY function as follows:

SELECT COUNT(*) FROM ab_logs GROUP BY scan_id

Upvotes: 2

Related Questions