Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chien_fu
Starting Member
16 Posts |
 Posted - 2007-12-11 : 12:58:41
|
| So.. I'm trying to add up the number of wins and losses from a schedule of games and list them with their corresponding team name from another table.Then I want to be able to sort by each teams number of wins. This is what I've got now, and it runs through without error, but it does not ORDER the list by "Wins"strSQL = "Select *, ((SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Win = true) + " & _"(SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Loss = true)) AS [Wins], " & _"(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Loss = true) + " & _"(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Win = true) AS [Losses], " & _"(SELECT Count(Tie) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Tie = true OR Game_Schedule.T2_ID = standings.ID AND Tie = true) AS [Ties] FROM standings ORDER BY T_Tier, T_Name, Wins DESC" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-11 : 13:25:28
|
[code]SELECT s.T_Tier, s.T_Name, SUM(CASE WHEN g.Win = 'True' THEN 1 ELSE 0 END) AS Wins, SUM(CASE WHEN g.Loss = 'True' THEN 1 ELSE 0 END) AS Losses, SUM(CASE WHEN g.Tie = 'True' THEN 1 ELSE 0 END) AS TiesFROM Game_Schedule AS gINNER JOIN Standings AS s ON s.ID IN (g.T1_ID, g.T2_ID)GROUP BY s.T_Tier, s.T_NameORDER BY s.T_Tier, s.T_Name, 3 DESC[/code]
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-12 : 00:57:27
|
| Also avoid having concatenated sql. Make use of stored procedure insteadMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
ORDER BY not working with subquery value