SQL Sum a Sum, or combine

i need help with the following query it should produce a sum of all the goals made in every match played:

SELECT SUM(stand1)
    FROM wedstrijdmodule_wedstrijden 
    WHERE team1 = 25 
UNION
SELECT SUM(stand2)
    FROM wedstrijdmodule_wedstrijden 
    WHERE team2 = 25;

it results in:

SUM(stand1)
68
77

I need:

SUM(stand1)
145

It's been a while since I did SQL and searched for about an hour, with no results. It doesn't have to look pritty because it will be extracted using php.

1 answer

  • answered 2017-10-11 10:03 jarlh

    Simple answer, wrap your query up in a derived table and do another SUM:

    select sum(sm)
    from
    (
        SELECT SUM(stand1) as sm
            FROM wedstrijdmodule_wedstrijden 
            WHERE team1 = 25 
        UNION ALL
        SELECT SUM(stand2)
            FROM wedstrijdmodule_wedstrijden 
            WHERE team2 = 25
    ) dt
    

    Switched to UNION ALL, in case the two select returns same number.

    Or, use case expressions to do conditional aggregation:

    SELECT SUM(case when team1 = 25 then stand1 else 0 end) + 
           SUM(case when team2 = 25 then stand2 else 0 end) 
    FROM wedstrijdmodule_wedstrijden 
    WHERE team1 = 25 or team2 = 25