Using MAX () and COUNT () in the same query

Question

I try to find out which language the user answers the most and return user_id , language_id in most cases and how many times they have answers.

I started with a SELECT table / subtable that returns these results:

 Table: `sub-selected` `user_id` `language_id` `answers` 1 1 1 2 1 1 1 2 5 2 2 2 1 4 3 1 5 1 

This table displays user_id , language_id and how many times the user language_id responded. I used this query to get it:

 SELECT t1.user_id, t2.to_language_id, COUNT(t2.to_language_id) as answers FROM translation_results as t1 LEFT JOIN translations as t2 ON t2.translation_id = t1.translation_id GROUP BY t2.to_language_id, t1.user_id 

Table structure:

 Table: `translations` `translation_id` `from_phrase_id` `to_language_id` Table: `translation_results` `translation_id` `result_id` PRI-AI `user_id` 

The translations table stores all the requested translations, and the translation_results table stores the responses to these translations and the corresponding user_id .

So, to summarize the table and get user_id, they were most answered by language_id , and how many times they answered in this language_id , I used:

 SELECT t1.user_id, t1.to_language_id, MAX(t1.answers) FROM ( //The sub-table SELECT t1.user_id, t2.to_language_id, COUNT(t2.to_language_id) as answers FROM translation_results as t1 LEFT JOIN translations as t2 ON t2.translation_id = t1.translation_id GROUP BY t2.to_language_id, t1.user_id ) as t1 GROUP BY t1.user_id, t1.to_language_id 

But this does not collapse the table into the desired structure and instead returns:

 Table: `sub-selected` `user_id` `language_id` `answers` 1 1 1 1 2 5 1 4 3 1 5 1 2 1 1 2 2 2 

I know that this depends on the group on two clauses , but if I only group_ user_id and do not include to_language_id in my selected columns, I cannot know which one corresponds to answer.id. I also tried subqueries and multiple joins, but I believe that I constantly need to use MAX(t1.answers) regardless of the selected columns and thus MAX(t1.answers) my hopes for group by matching correctly. How can I collapse the query correctly, instead of group by finding all the unique combinations of MAX() user_id and to_language_id ?

Answer 1

To obtain:

the user_id , they were most answered by language_id , and how many times they answered in this language_id

you can use variables:

 SELECT user_id, language_id, answers FROM ( SELECT user_id, language_id, answers, @rn:= IF(@uid = user_id, IF(@uid:=user_id, @rn: =@rn +1, @rn: =@rn +1), IF(@uid:=user_id, @rn:=1, @rn:=1)) AS rn FROM (SELECT t1.user_id, t2.to_language_id AS language_id, COUNT(t2.to_language_id) as answers FROM translation_results as t1 LEFT JOIN translations as t2 ON t2.translation_id = t1.translation_id GROUP BY t2.to_language_id, t1.user_id ) t CROSS JOIN (SELECT @rn:=0, @uid:=0) AS vars ORDER BY user_id, answers DESC ) s WHERE s.rn = 1 

However, there is a limitation in the above query: if there is more than one language_id having the same maximum number of responses for user_id , then only one will be returned.

Demo here

An alternative way is to use the query twice as a view:

 SELECT t1.user_id, language_id, t1.answers FROM (SELECT t1.user_id, t2.to_language_id AS language_id, COUNT(t2.to_language_id) as answers FROM translation_results as t1 LEFT JOIN translations as t2 ON t2.translation_id = t1.translation_id GROUP BY t2.to_language_id, t1.user_id ) t1 INNER JOIN ( SELECT user_id, MAX(answers) AS answers FROM (SELECT t1.user_id, t2.to_language_id, COUNT(t2.to_language_id) as answers FROM translation_results as t1 LEFT JOIN translations as t2 ON t2.translation_id = t1.translation_id GROUP BY t2.to_language_id, t1.user_id ) t GROUP BY user_id ) t2 ON t1.user_id = t2.user_id AND t1.answers = t2.answers 

This request does not have the limitations of the previous request, but is likely to be less efficient than the previous one.

Demo here

Answer 2

If I asked your question, you should define a temporary or sub_selected table with the result of the subquery, giving a call to sub_selected , then you should do:

 SELECT t1.user_id, t1.to_language_id, answers FROM sub_selected as t1 WHERE t1.answers = (SELECT MAX(answers) FROM sub_selected t2 WHERE t1.user_id = t2.user_id and t1.to_language_id = t2.language_id)