CODEWITHSUNDEEP
sqlsortingsum
mp42871
mp42871

Reputation: 191

SQL sort based on sum of various columns

I have a MS-SQL table of game results which contains a home team, home team score, away team, and away team score. I am attempting to query the teams, wins, and losses ordered by first, total wins(descending) and then by total losses(ascending). The table does not contain a wins or losses column. Wins and losses must be calculated based on the score. If I need to create a view or temporary table, how do I create that view with teams, wins, and losses?

Upvotes: 0

Views: 3787

Answers (4)

mp42871
mp42871

Reputation: 191

select 
    teamName, 
    'wins' = 
        case 
            when wins is null then 0 
            else wins 
        end, 
    'losses' = 
        case 
            when losses is null then 0 
            else losses 
        end 
from 
    teams 
left join 
( 
    select 
        a.wins as wins, 
        b.losses as losses, 
        'team' = 
            case 
                when a.team is null then b.team 
                else a.team 
            end 
    from 
    ( 
        select 
            homeTeam as team, 
            sum(wins) as wins 
        from 
        ( 
            select 
                homeTeam, 
                count(*) as wins 
            from 
                results 
            where 
                homeScore > awayScore 
            group by 
                homeTeam 
            union all 
            select 
                awayTeam, 
                count(*) as wins 
            from 
                results 
            where 
                homeScore < awayScore 
            group by 
                awayTeam         
        ) a 
        group by homeTeam 
    ) a 
    full outer join 
    ( 
        select 
            homeTeam as team, 
            sum(losses) as losses 
        from 
        ( 
            select 
                homeTeam, 
                count(*) as losses 
            from 
                results 
            where 
                homeScore < awayScore 
            group by 
                homeTeam 
            union all 
            select 
                awayTeam, 
                count(*) as losses 
            from 
                results 
            where 
                homeScore > awayScore 
            group by 
                awayTeam 
        ) a 
        group by 
            homeTeam 
    ) b 
    on a.team = b.team 
) c 
on teams.id = c.team

Upvotes: 0

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56408

Assuming you can pull back that information in your query, you can use aggregates in ORDER BY clauses:

ORDER BY
    SUM(win) DESC
   ,SUM(loss) ASC

edit: update for comment

With only the scores being stored, you'll want to either use a temporary table or a CTE (depends on the DBMS you are using if the support them) to first create a table that has the teams and wins and losses in them, and then you can SELECT from that and ORDER accordingly.

Upvotes: 1

MJB
MJB

Reputation: 7686

Homework?

Anyway, you can specify the sort order in a SQL statement by following the column with the order. For example,

select x, y, z from table order by x asc, y desc, z asc

Upvotes: 0

JNK
JNK

Reputation: 65187

SELECT *
FROM MyTable
WHERE ...
ORDER BY SUM(win) DESC, SUM(loss) ASC

For a more detailed answer, provide your schema and sample data.

Upvotes: 1

Related Questions