Problems with max use after count

Use your query as a subquery, as follows.

SELECT id_pregunta, tipo, max(cantidad_respuesta) as cantidad_respuesta FROM (
    SELECT p.id_pregunta, tr.tipo, COUNT(r.id_respuesta) as 
    cantidad_respuestas
    FROM encuesta e 
    LEFT JOIN dimension d on e.id_encuesta = d.id_encuesta_p
    LEFT JOIN preguntas p on d.id_dimension = p.id_dimension_p
    LEFT JOIN tipo_respuesta tr on p.id_pregunta = tr.id_pregunta_tr 
    LEFT JOIN respuesta r on tr.id_tipo = r.id_tipo_r
    LEFT JOIN form_alumnos f on r.id_form_alumno_fk = f.id_form_alumno 
    WHERE (f.cod_curso_alumno = '367845' or cod_curso_alumno IS NULL) 
            AND (d.id_dimension = '1')
            GROUP BY p.id_pregunta, tr.tipo
) WHERE cantidad_respuesta <> 0 GROUP BY tipo; 

Greetings.

I agree with @folivaresrios that, in one way or another, you need to use your current query as a subquery. But getting the result you want is not as simple as it seems.

If the desired result was only:

id_pregunta  CANTIDAD_RESPUESTAS
----------   -------------------
1                   3
2                   4

... then it would be simple. It would only take a GROUP BY + MAX :

select id_pregunta, max(CANTIDAD_RESPUESTAS)
from (
    -- aquí pones tu consulta actual
) t
group by id_pregunta
order by id_pregunta

Fiddle

But your result needs to include all columns (that is, the column tipo too) of the record whose value for cantidad_respuestas is the highest for the grouping of id_pregunta . That's the uncomfortable part.

Many would be tempted to simply add the column tipo to SELECT , which MySQL accepts without complaint in versions prior to 5.7:

select id_pregunta, tipo, max(CANTIDAD_RESPUESTAS)
from (
    -- aquí pones tu consulta actual
) t
group by id_pregunta
order by id_pregunta

Fiddle

... but although this is something that is commonly seen, it is an error. As you can see in the Fiddle, the value for tipo is incorrect.

In fact, MySQL documentation specifies that you include a column in the SELECT that is not used by an aggregation function (such as MAX , or SUM ) and that is not part of the GROUP BY produces a result that can not be predicted (Quote in English: the values chosen are indeterminate ).

Solution

Usually, the way to solve this type of queries is by using the window function ROW_NUMBER() , which many databases include. But MySQL has no window functions.

Because of this, the only really correct solution is the following:

select id_pregunta, min(tipo) as tipo, cantidad_respuestas
  from (
      -- aquí pones tu consulta actual
  ) t
 where (id_pregunta, cantidad_respuestas) in (
    select id_pregunta, max(cantidad_respuestas)
    from (
       -- aquí también repites tu consulta actual
    ) t2
    group by id_pregunta)
group by id_pregunta, cantidad_respuestas
order by id_pregunta

Fiddle

... but as you can see, although the query produces the correct result, it is extremely inefficient, because it forces you to repeat your complicated query 2 times. In addition, the query is required to define an additional GROUP BY to eliminate duplicate records when more than one record has the same maximum number of responses.

There is another alternative that is much more efficient, that uses variables to simulate a window function:

set @rnk := 0;
set @id_pregunta := -1;
select id_pregunta,
       tipo,
       cantidad_respuestas
  from (select tipo,
               cantidad_respuestas,
               @rnk := case when @id_pregunta = id_pregunta then @rnk + 1 else 1 end as rnk,
               @id_pregunta := id_pregunta as id_pregunta
          from (
                 -- aquí pones tu consulta actual
                 -- Importante: también debes agregar este ORDER BY aquí
                 order by p.id_pregunta, cantidad_respuestas desc, tr.tipo
               ) t
        ) t
 where rnk = 1
 order by id_pregunta;

Fiddle

If you use this query, make sure that you add a ORDER BY to your original query when you add it as a subquery.

Now, it is important to mention that this query depends on MySQL evaluating the expressions with variables in the order that appear in SELECT . According to the MySQL documentation , this is not guaranteed (Quote in English: < em> the order of evaluation for expressions involving user variables is undefined ). In practice, it seems that the variables are evaluated in order. But who knows if this behavior will not change in a future version of MySQL. So if you prefer to avoid this risk, I suggest using the first option that is less efficient, but with guaranteed correct results.