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

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