If statements in mysql?

[EchoFool]

If there a way to add if statements to mysql queries?

 

I want to add if statements to the ORDER BY clause .. heres an example of my idea:

 

ORDER BY (

Listing.Ratings  * number of rows with the value of one +

Listing.Ratings  * number of rows with the value of two +

Listing.Ratings  * number of rows with the value of three +

Listing.Ratings  * number of rows with the value of four +

Listing.Ratings  * number of rows with the value of five

) AS TotalScore DESC

 

Obviously this is not in MYSQL syntax but is this possible? My current quiery is this:

 

<?php
$SELECT = mysql_querY("SELECT listings.*
FROM listings 
INNER JOIN listratings ON
	listings.GameID=listratings.GameID
WHERE listings.Authorised='1'")
Or die(mysql_error());
?>

 

Any idea how i can add such a complex ORDER BY idea?

[Ken2k7]

That makes no sense. Try a different approach in asking a question. Explain what you're trying to do.

[EchoFool]

Well im trying to use a more accurate AVG() by using maths involved.... so say you got a table:

 

ID |  Voted  |

1  |    4    |

1  |    5    |

1  |    5    |

2  |    5    |

 

 

What i want to do is order by sum of (Totalrows multiplied by VotedFor that equals 1) + (totalrows multiplied by VotedFor that equals 2) ( up to 5) AS TotalScore

Then use that total score in an ORDER CLAUSE

 

In the above table the result would be:

 

(5 * 2) + (1 * 4) = 14 for ID 1

(1 * 5) = 5 for ID 2

 

ORDER BY DESC

 

ID 1 = 1st

ID 2 = 2nd.

[Ken2k7]

Try

SELECT l1.ID AS id,
     (SELECT SUM(l2.Voted) FROM listings l2 WHERE l2.ID = l1.ID) AS sum
FROM listings l1
GROUP BY l1.ID
ORDER BY sum DESC;