CODEWITHSUNDEEP
phpmysqlsql
Xaretre
Xaretre

Reputation: 65

Taking a specific data with MySQL, Group by

I want to take most frequently entered datas in last 1 day.

Here is my code:

SELECT tag_id, time, 
COUNT(tag_id) AS value_occurrence  
FROM   tag_rel 
GROUP BY tag_id 
HAVING time > '$yesterday' 
ORDER BY value_occurrence DESC LIMIT 10

Code is working but it has a problem:

I wanna take between july 22 and 23.

id| tag_id     | time       |
1 | football   | 22,5 july  |  
2 | basketball | 22,5 july  |  
3 | football   |  22,5 july |  
4 | football   |  21 july   |

I want to take first three rows and order them by their frequency.

ex: 1- football 2- basketball

But my code is not taking "football". It just shows "basketball". Because additionally "football" has a time value which is smaller than 22 july.

How can solve it?

Upvotes: 2

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

The correct query for what you want would use where, not having:

SELECT tag_id, MAX(time) as maxtime, 
       COUNT(tag_id) AS value_occurrence  
FROM   tag_rel 
WHERE time > '$yesterday' 
GROUP BY tag_id 
ORDER BY value_occurrence DESC
LIMIT 10;

Otherwise, there is an arbitrary value used for time, both in the select and the having clause.

Upvotes: 1

Related Questions