CODEWITHSUNDEEP
phpmysql
Sarath TS
Sarath TS

Reputation: 2462

How to get count of a particular item from table 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.

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

Upvotes: 0

Views: 177

Answers (3)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

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

Upvotes: 1

juergen d
juergen d

Reputation: 204746

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 0

Related Questions