sam sam sam sam - 3 months ago 7
MySQL Question

How to get count of data using mysql

I have two table data and user. I need to get count of data->value and show only greatest count and user->name.

eg, user_id 2: count is 3

I tried some query but it's wrong.

SELECT data.value, user.name
FROM data
INNER JOIN user
ON data.value = user.id
WHERE data.field = 'user_id'


DATA

id order_id field value (id of user table)
1 1 user_id 1
2 2 user_id 3
3 3 user_id 2
4 4 user_id 2
5 5 user_id 1
6 6 user_id 2


USER

id name
1 foo
2 joo
3 peter

Answer
  • Data is dependent on user's records and each user has multile associated rows in the data table, we need to use GROUP BY.
  • We need to find the user with the maximum count. For that, the count column is arranged in descending order using ORDER BY count DESC and the very first row with the max. value is extracted using LIMIT 0, 1.

Try this:

SELECT data.value, user.name, data.field, COUNT(*) as count
FROM data 
INNER JOIN user 
ON data.value = user.id 
GROUP BY user.name
HAVING data.field = 'user_id'
ORDER BY count DESC
LIMIT 0,1

Output:

value  |  name  |  field  |  count
  2    |  john  | user_id |    3
Comments