Count multiple criteria in separate columns - MySQL

Question

I have the following table and am trying to count the number of actions each user has performed.

----------------------------------
| ID | User         | Action     | 
----------------------------------
| 1  | BobDoe       | View       |
| 2  | BobDoe       | Edit       |
| 3  | JaneDoe      | Comment    |
| 4  | BobDoe       | Comment    |
| 5  | JohnSmith    | Edit       |
| 6  | JaneDoe      | Edit       |
| 7  | JohnSmith    | Comment    |
| 8  | BobDoe       | View       |
----------------------------------

Currently I use the following query to just get the number of edits, but I'm wanting to change it so it counts comments and views and displays them in their own columns, I have no clue how I'd go about counting them each separately without having to make an entirely new query.

SELECT Type, User, COUNT(*) AS Num FROM some_database GROUP BY User

Any ideas?

Type is is Action, sorry I had changed the name and forgot to update it in the example I posted. — Scott MacDonald
– Scott MacDonald, Commented Nov 30, 2011 at 23:29

Mark Byers · Accepted Answer · 2011-11-30 23:05:42Z

9

Try this query that works in MySQL because TRUE is equivalent to 1 and FALSE is equivalent to 0:

SELECT
    User, 
    COUNT(*) AS Num,
    SUM(Action = 'Comment') AS NumComments,
    SUM(Action = 'View') AS NumViews
FROM some_table
GROUP BY User

Result:

User       Num  NumComments  NumViews
-------------------------------------
BobDoe     4    1            2       
JaneDoe    2    1            0       
JohnSmith  2    1            0

edited Nov 30, 2011 at 23:05

answered Nov 30, 2011 at 23:00

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Flimm Over a year ago

If the table is empty, SUM(boolean) will return NULL, not zero.

Ben Lee · Accepted Answer · 2011-11-30 23:06:14Z

One thing you can do is group by both User and Action like this:

SELECT *, COUNT(*) AS Num FROM some_database GROUP BY User, Action

That will return results like this:

----------------------------------------
| User         | Action     | Num | 
----------------------------------------
| BobDoe       | View       | 2   |
| BobDoe       | Edit       | 1   |
| JaneDoe      | Comment    | 1   |
| BobDoe       | Comment    | 1   |
| JohnSmith    | Edit       | 1   |
| JaneDoe      | Edit       | 1   |
| JohnSmith    | Comment    | 1   |
----------------------------------------

So it won't give the results in their own columns, but it will give the count for each user/action combo as its own row.

The nice thing about this solution is that it will scale to any number of actions (Edit, View, Comment, and any other actions you want in the future). If you don't need that scalability, @Mark Byers answer is the way to do it and return the counts as columns.

Collectives™ on Stack Overflow

Count multiple criteria in separate columns - MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related