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

I am trying to figure out what language a user answers in the most, and return by user_id, the language_id they answer in the most and how many times they have answers.

I began by SELECTing a table/sub-table which 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 returns the user_id, the language_id, and how many times that language_id has been answered by the user. 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

The table structure is:

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 translations requested, and the translation_results table stores the answers to those translations and the respective user_id.

So, to sum up the table and to get the user_id, their most answered language_id, and how many times they answered in that 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 strucutre 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 it is affected by the group by of two clauses, but then if I only group by user_id and do not include to_language_id in my selected columns, I can’t know which respective language_id is the most answered. I have also tried sub-queries and a few joins, but I find I constantly need to use MAX(t1.answers) regardless in the selected columns and thus destroys my hopes of collasping the group by correctly. How can I collapse the query correctly instead of having group by find all the unique MAX() combinations of user_id and to_language_id?


Source: mysql

Leave a Reply