CODEWITHSUNDEEP
mysqlsqlgroup-by
margherita pizza
margherita pizza

Reputation: 7145

MySQL count of the grouped two columns

I have this user_logs table:

user_id | operation | table
--------+-----------+-----------
1       | create    | restaurant
1       | create    | restaurant
1       | create    | dish type
1       | create    | dish type
1       | create    | dish type
1       | update    | dish name
1       | update    | dish name
1       | update    | dish name
1       | update    | restaurant
1       | update    | restaurant

I want to populate data for this table:

operation_on_table | count
-------------------+------
create restaurant  | 2
create dish type   | 3
update dish name   | 3
update restaurant  | 2

What I have done upto now is:

SELECT operation, count(operation) FROM user_logs
WHERE user_id = 1
GROUP BY operation

It gives me this:

operation | count
----------+------
create    | 5
update    | 5

But this is not what I need.

How can I add table name to the count?

Upvotes: 1

Views: 64

Answers (4)

Amit Singh
Amit Singh

Reputation: 220

You can try

select operation,[table], count(operation) from  user_logs 
GROUP BY operation,[table]

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272106

GROUP BY both columns and concatenate the columns inside select:

SELECT CONCAT_WS(' ', `operation`, `table`) AS `operation_on_table`
     , COUNT(*) AS `count`
FROM `user_logs`
WHERE `user_id` = 1
GROUP BY `operation`, `table`

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

Simple query would do the work:

select operation, count(*) from (
   select concat(operation, ' ', `table`) `operation` from user_logs
) a group by operation

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

add table column in group by

SELECT operation,`table`, count(operation) 
FROM user_logs
WHERE user_id = 1
GROUP BY operation,`table`

Upvotes: 3

Related Questions