Need to order by Sum(rate)

[miguelfsf]

Hello,

 

Lets if i can explain this well (sorry for bad english!)

 

I have two differents tables on my db "Mixtapes" and "Vote" and i want to order them in DESC order by the SUM of all rate values that belong to a specific mixtape.

 

Mixtapes:

id

name

status

(etc)

 

Vote

id_mixtape  // id_mixtape = id in Mixtapes table

rate

 

This is the query that im using but its not listing in Desc order, dont know why:

"SELECT id,name FROM mixtape WHERE status=1 AND id IN(SELECT id_mixtape FROM vote GROUP BY id_mixtape ORDER BY SUM(rate) DESC) LIMIT 12"

[Barand]

You need to JOIN the tables. In this case use LEFT JOIN as there may be mixtape with no vote records

SELECT m.id, m.name, SUM(v.rate) as total 
FROM mixtape m
    LEFT JOIN vote v ON  m.id = v.id_mixtape
WHERE m.status=1 
ORDER BY total DESC
LIMIT 12

[miguelfsf]

Didnt work.. It has only listed one mixtape with the sum of all rates, but i want the rate that is specific to that mixtape

 

 

I tested the sql code on my phpmyadmin and for total i got 38 and it should be 6. 

For example, this is what i have on my vote table:

 

 

 

The total of the id_mixtape number 4 should be 20+12=32 and the total of id_mixtape number 3 should be 6, and then it orders each mixtape by this value on DESC order.

[Barand]

Sorry - missed the important bit

SELECT m.id, m.name, SUM(v.rate) as total 
FROM mixtape m
    LEFT JOIN vote v ON  m.id = v.id_mixtape
WHERE m.status=1
GROUP BY m.id 
ORDER BY total DESC
LIMIT 12

[miguelfsf]

Thanks alot it worked