SQL - group by, count

Question

SQL - group by, count

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good afternoon, I currently have the following code and result. Instead of having this result, I would rather have only one line for each type (column 1), this line 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 only the 1st and 3rd line. Basically group this. Thanks

sql group-by order

asked by anonymous 20.05.2018 / 14:56

2 answers

registration procedure with error in if chained Display the node data in a ListView Firebase

score 0 · Answer 1

For this, you will have 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;

score 0 · Answer 2

As far as I understand, you can tell which vehicle has made the most trips. So, you run this query in a subquery and the main query is done over the id of the vehicle (detail, the query works so if it does not carry different types, like example 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;