CODEWITHSUNDEEP
mysqlsql
Mike Thrussell
Mike Thrussell

Reputation: 4515

mysql query - counting like terms

My MySQL table is as follows:

ID  term  timestamp

1   foo    xxxx
2   bar    xxxx
3   bar    xxxx
4   foo    xxxx
5   foo    xxxx
6   foo    xxxx
7   xxx    xxxx

I wish to produce a list displaying the most common terms in order of quantity of that term

i.e. the end result:

foo 
bar
xxx

What query should I use ? I've done some googling, but I'm not quite sure what I'm looking for - COUNT / GROUP BY etc.

Upvotes: 0

Views: 338

Answers (6)

zerkms
zerkms

Reputation: 254934

The following SQL query should provide a solution to your problem.

SELECT term
FROM tablename
GROUP BY term
ORDER BY COUNT(*) DESC

Upvotes: 4

comodoro
comodoro

Reputation: 1566

This SQL query should do it:

SELECT term FROM my_table GROUP BY term ORDER BY COUNT(term) DESC;

Upvotes: 0

Mathew Thompson
Mathew Thompson

Reputation: 56429

GROUP BY is correct. Try this:

SELECT term FROM table
GROUP BY term
ORDER BY COUNT(term) desc

Upvotes: 1

rs.
rs.

Reputation: 27427

SELECT TERM
FROM @table
GROUP BY TERM
HAVING COUNT(*) > 5 --replace with number you want
ORDER BY COUNT(*)

Upvotes: 0

bitoshi.n
bitoshi.n

Reputation: 2318

Try

SELECT term, Count(*) AS cnt FROM mytable GROUP BY term ORDER BY cnt DESC

Upvotes: 1

f1sherman
f1sherman

Reputation: 452

SELECT COUNT(*), term FROM table GROUP BY term ORDER BY COUNT(*) DESC;

Upvotes: 2

Related Questions