ordering mysql query results

[Destramic]

hey guys i have two tables called teams and results and basically the the results table will store the results of each match the team has.

 

what i need to do is when i select all the teams i need the team with the most results to be ordered first and descending from there....example

 

------------------

man united  12 pts

liverpool      9 pts

etc etc

------------------

 

the tables im using are below if anyone could tell me the best way of calculating the results and ordering them please...thank you

 

teams

-------------

team_id

team_name

-------------

 

results

-------------

result_id

team_id

result

-------------

[wildteen88]

You'll want to join the two tables together using a simple MySQL Join, eg

SELECT t.team_id, 
       t.team_name,
       COUNT(r.results) AS team_points 
FROM teams t 
LEFT JOIN results r ON t.team_id = r.team_id
GROUP BY t.team_id
ORDER BY team_points DESC

The above is an untested query. Give it a go and see what you get.

[Destramic]

i think this is almost what i need...the result will contain three values....win...loss...draw

so i need to count these three and then order by them instead of just counting result....is this possible please wildteen

[wildteen88]

A small change

SELECT t.team_id, 
       t.team_name,
       SUM(r.results) AS team_points, 
       SUM(r.win) AS team_wins, 
       SUM(r.loss) AS team_losses, 
       SUM(r.draw) AS team_draws,  
FROM teams t 
LEFT JOIN results r ON t.team_id = r.team_id
GROUP BY t.team_id
ORDER BY team_points DESC

[jcbones]

I don't think his table has columns "win", "loss", or "draw".  Rather I think the table column "results" contain those values.

 

You may need a CASE for this to work.

 

UN-TESTED

SELECT t.team_id,
       t.team_name,
       COUNT(r.result_id) as 'team_points',
       COUNT(CASE r.results
       WHEN 'win' THEN 'team_wins'
       WHEN 'loss' THEN 'team_losses'
       ELSE 'team_draws' END)
FROM teams t
LEFT JOIN results r ON t.team_id = r.team_id
GROUP BY t.team_id
ORDER BY team_points DESC

[Destramic]

thanks jcbones...what i need the sum of the win draws and losses (like SUM(r.win) AS team_wins)....and this way i can order the query by wins, losses and draws asscendingly to get the correct results please

[Destramic]

ive been trying to work it out mysql and ive got it working but im not sure if this is the best way....im new to the case statement so this might be a bit long winded if you could help

 

SELECT t.team_id, t.team_name, COUNT( r.league_match_result_id ) AS 'team_matches_played', COUNT(
CASE r.result
WHEN 'Win'
THEN 'team_wins'
END ) AS 'team_wins', COUNT(
CASE r.result
WHEN 'Draw'
THEN 'team_draws'
END ) AS 'team_draws', COUNT(
CASE r.result
WHEN 'Loss'
THEN 'team_losses'
END ) AS 'team_losses'
FROM teams t
LEFT JOIN league_match_results r ON t.team_id = r.team_id
GROUP BY t.team_id
ORDER BY team_wins DESC, team_draws DESC

[jcbones]

 

Try:

SELECT t.team_id, t.team_name, COUNT( r.league_match_result_id ) AS 'team_matches_played', COUNT(
CASE r.result
WHEN 'Win'
THEN 1
END ) AS 'team_wins', COUNT(
CASE r.result
WHEN 'Draw'
THEN 1
END ) AS 'team_draws', COUNT(
CASE r.result
WHEN 'Loss'
THEN 1
END ) AS 'team_losses'
FROM teams t
LEFT JOIN league_match_results r ON t.team_id = r.team_id
GROUP BY t.team_id
ORDER BY team_wins DESC, team_draws DESC

 

It might not make a difference, but it is correct, as you are wanting to count and not change the column.

[Destramic]

thanks alot again...also one last thing when i processing these results is it good practice to do it with <div> and styles or just do it using a <table>?