How to get count of a particular item from table using mysql

Question

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

I tried some query but it's wrong.

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

DATA

id  order_id   field     value
 1      1      user_id     1
 2      2      user_id     1
 3      3      vehicle_id  1
 4      4      user_id     1
 5      5      vehicle_id  2    
 6      6      user_id     2

USER

id    name  
 1    foo
 2    joo
 3    peter

Current result is

value   name    field   count   
1       foo     user_id   4

expecting result is

value   name    field   count   
 1      foo     user_id   3

**Related:** (?) http://stackoverflow.com/questions/10363518/select-max-from-count — Script47, Aug 21 '16 at 14:23
@ZainFarooq, Why we need 4 id. data.value is related to user table. — Sarath TS, Aug 21 '16 at 14:26

score 1 · Accepted Answer · answered Aug 21 '16 at 14:25

Use where instead of having.
With where you filter the data. But using having takes all records and filter out complete groups and not single records

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

score 1 · Answer 2 · answered Aug 21 '16 at 14:40

You must group not only by user.name, but also by data.field and data.value, then you will get the desired result, see (sqlfiddle)

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

score 0 · Answer 3 · answered Aug 21 '16 at 14:25

0

You should add filter ond data.field also

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

answered Aug 21 '16 at 14:25

ScaisEdge

131,976
10
91
107

How to get count of a particular item from table using mysql

3 Answers3