How to sort by average and quantity (weight)?

Asked

Viewed 323 times

3

I have this query on 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

Brings me that result:

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

I can’t order by MEDIA and QTD_AVALIACOES and not the other way around, QTD_AVALICOES and MEDIA. If I do it in one of these ways, it might work in some cases and not in others.

So I need to sort by average weight. But I don’t 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 proportionally it is better.

How do I do that ?

mysql
  • 1

    Want to sort by the result MEDIA*QTD_AVALIACOES on each right line?

  • I want to make a weighted average, if I’m not mistaken. I can’t explain, I’m Newbie.

  • 1

    Don’t say that. Okay but what do I calculate? MEDIA*QTD_AVALIACOES on each line? what mathematical operation do you want to do to then sort?

  • I want to calculate the average weight. I believe that’s what you said.

  • But what weight? What is the weight?... I will put a solution to the problem, if not say to withdraw the answer

  • Why did you remove the reply @Miguel. It had helped me.

  • Ha I’m sorry, I thought I was wrong and that not what I wanted... I’ll put

  • It’s just that I’m doing tests, so I didn’t give feedback.

  • Okay, that’s it. I’m sorry

Show 4 more comments

2 answers

2


See if this solves, multiply the media by way of qtd_avaliacoes respective:

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;

  • This makes a mistake: Unknown column 'media' in 'field list'

  • Ha sorry... Distraction from my part @Diegosouza. I will mend

  • It’s just that I can’t vote up because I don’t have 15 reputation points. But it worked out what you said. I hadn’t thought about it. Thank you

  • It doesn’t matter @Diegosouza, Obgado, when you vote ;P

1

A 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

I mean, it’s exactly your query, I merely altered the order by.

  • Still not it. I did so in my query above.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.