SQL - group by, Count

Question

Good afternoon, I currently have the following code and result. I would like instead of having this result, to have only one row for each type (column 1), this row being the one whose column 4 has the highest value. If anyone can help (I’m a beginner).

SELECT DISTINCT TIPO.NOME_TIPO_MERCADORIA AS TIPO, VEI.ID_VEICULO AS IDVeiculo, 
                VEI.MATRICULA AS matricula, COUNT(VIA.ID_VIAGEM) AS Viagens 
FROM VEICULO VEI, TIPO_MERCADORIA TIPO, VIAGEM VIA
WHERE VEI.ID_VEICULO = VIA.ID_VEICULO
AND VEI.ID_TIPO_MERCADORIA = TIPO.ID_TIPO_MERCADORIA
GROUP BY TIPO.NOME_TIPO_MERCADORIA, VEI.ID_VEICULO, VEI.MATRICULA
ORDER BY 1, 4 DESC;
/

I would like the final results to be just the 1st and 3rd line. Basically group this. Thank you

Answer 1

From what I understand, you can know which vehicle made the most trips. So, play this search in a subconsultation and the main consultation is done on top of the id of the vehicle (detail, the query works like this in case the same does not transport different types, as exemplified in the question).

SELECT DISTINCT TIPO.NOME_TIPO_MERCADORIA AS TIPO, 
    VEI.ID_VEICULO AS IDVeiculo, 
    VEI.MATRICULA AS matricula, 
    COUNT(VIA.ID_VIAGEM) AS Viagens 
FROM VEICULO VEI, TIPO_MERCADORIA TIPO, VIAGEM VIA
WHERE VEI.ID_VEICULO = VIA.ID_VEICULO
  AND VEI.ID_TIPO_MERCADORIA = TIPO.ID_TIPO_MERCADORIA
  AND VEI.ID_VEICULO = 
    (SELECT TOP 1 VEI2.ID_VEICULO 
     FROM VEICULO VEI2, VIAGEM VIA2 
     WHERE VEI2.ID_VEICULO = VIA2.ID_VEICULO
       AND VEI2.ID_VEICULO = VEI.ID_VEICULO 
     GROUP BY VEI2.ID_VEICULO 
     ORDER BY COUNT(VIA2.ID_VIAGEM) DESC)
GROUP BY TIPO.NOME_TIPO_MERCADORIA, VEI.ID_VEICULO, VEI.MATRICULA
ORDER BY 1 DESC;

Answer 2

For this, you will need to use a sub-query:

SELECT TIPO.NOME_TIPO_MERCADORIA AS TIPO, b1.ID_VEICULO AS IDVeiculo, b1.MATRICULA AS matricula, b1.QTDE_VIAGENS AS Viagens
FROM TIPO a
INNER JOIN (
    SELECT a1.ID_VEICULO, a1.MATRICULA COUNT(b1.ID_VIAGEM) QTDE_VIAGENS
    FROM VEICULO a1
    INNER JOIN VIAGEM b1 ON a1.ID_VEICULO = b1.ID_VEICULO
    WHERE a.ID_TIPO_MERCADORIA = a.ID_TIPO_MERCADORIA
    GROUP BY a1.ID_VEICULO
    ORDER BY QTDE_VIAGENS DESC
    LIMIT 1
) b1;