How to sort by average and quantity (weight)?

Question

How to sort by average and quantity (weight)?

Navigation

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

3

I have this query in MySql :

SELECT
    dealers.id,
    COALESCE (AVG(rating), 0) AS media,
    COALESCE (COUNT(dealer_ratings.id), 0) AS qtd_avaliacoes
FROM
    'dealers'
LEFT JOIN 'dealer_ratings' ON 'dealer_ratings'.'id_concessionaria' = 'dealers'.'id'
AND 'id_status' IN (1, 2)
WHERE
    'id_cidade' = 5141
AND 'confirmada' = 1
GROUP BY
    'dealers'.'id'
ORDER BY
    'media' DESC,
    'qtd_avaliacoes' DESC

It brings me this result:

ID      Média   Quantidade
11393   5.0000      1
12291   4.5000      12
11918   0.0000      0
14078   0.0000      0

I can not sort by MEDIA and QTD_AVALIACOES and not by contrast QTD_AVALICOES and MEDIA . If I do it in one of these ways, it might work in some cases, not in others.

So I need to sort by average weight. But I do not know what calculation to do.

In the case of the example above, the result with an average of 4.5 and 12 of quantity would be to be in first, because proportionately is better.

How do I do this?

mysql

asked by anonymous 19.08.2016 / 13:38

2 answers

1

One way would be:

SELECT
    dealers.id,
    COALESCE (AVG(rating), 0) AS media,
    COALESCE (COUNT(dealer_ratings.id), 0) AS qtd_avaliacoes 
FROM  'dealers' 
    LEFT JOIN 'dealer_ratings' ON 'dealer_ratings'.'id_concessionaria' = 'dealers'.'id' AND 'id_status' IN (1, 2)         
WHERE
    'id_cidade' = 5141 AND 'confirmada' = 1 
GROUP BY
    'dealers'.'id' 
ORDER BY
    COALESCE (AVG(rating), 0)  DESC,
    COALESCE (COUNT(dealer_ratings.id), 0) DESC

That is, it's exactly your query , I've just changed order by .

19.08.2016 / 13:53

How do I keep the Look And Feel selected in the JComboBox after the window is closed? How to get the first result of duplicate rows?

score 2 · Accepted Answer

See if this resolves, multiplying media by qtd_avaliacoes thereof:

SELECT (AVG(rating), 0)*(COUNT(dealer_ratings.id), 0) as result FROM dealers ORDER BY result DESC;

Adapted to your case:

SELECT
    dealers.id,
    COALESCE (AVG(rating), 0) AS media,
    COALESCE (COUNT(dealer_ratings.id), 0) AS qtd_avaliacoes,
    (AVG(rating), 0)*(COUNT(dealer_ratings.id), 0) as result
FROM
    'dealers'
LEFT JOIN 'dealer_ratings' ON 'dealer_ratings'.'id_concessionaria' = 'dealers'.'id'
AND 'id_status' IN (1, 2)
WHERE
    'id_cidade' = 5141
AND 'confirmada' = 1
GROUP BY
    'dealers'.'id'
ORDER BY result DESC;