JoTaRo JoTaRo - 6 months ago 11
SQL Question

How to find the most appearances of a value having count > 7

table name: d_risposte
id: index, unique not relevant
reply: from 1 to 4

id | reply | user
-----------------
| 1 | a34
| 2 | a34
| 1 | a34
| 3 | a34
| 2 | a34
| 4 | a34
| 1 | a34
| 1 | a34
| 4 | a55
| 2 | a55
| 2 | a55
| 4 | a71
| 4 | a71
| 1 | a71
| 3 | a71
| 4 | a71
| 2 | a71
| 4 | a71
| 4 | a71
| 2 | b22
| 4 | b22
| 2 | b22


i want keep only users with begin with "a" and has at least 7 reply,
then
i want count the majority of kind of replay 1..4 in this case the result should be:

user | top_reply
----------------
a34 | 1 (becouse the 1 reply appears 4 times)
a71 | 4 (becouse the 4 reply appears 5 times)


(if it's tie, doent matters)

so my query is:

SELECT user FROM `d_risposte` WHERE user LIKE 'a%' group by user having count(*) > 7


the table result should be:

user
----
a34
a71


then i should count the majority of reply:
but i dont know how to do in just one query,
i try something like that...

SELECT reply, COUNT(reply) AS fooCount FROM d_risposte WHERE user ????????? GROUP BY reply ORDER BY COUNT(reply) DESC LIMIT 1


i cant figure it out :(

Answer

That's an interesting question, try this:

SELECT DISTINCT t.user,
       (SELECT s.reply FROM d_risposte s
        WHERE s.user = t.user
        GROUP BY s.reply
        ORDER BY count(*) DESC limit 1) as top_reply
FROM d_risposte t
INNER JOIN(SELECT d.user FROM d_risposte d
           WHERE d.user like 'a%'
           GROUP BY d.user
           HAVING count(*) > 6) t2
 ON(t2.user = t.user)

This will basically select for each user the top_reply , and then join to a table to filter those who have less then 7 records and doesn't start with a

Comments