Can I join two Select sum SQL queries from the same table but with different filters?

Question

I am running into a problem where I am trying to add two SELECT sums from the same table but with different conditions into one single result.

Here is the code:

DROP TABLE Match CASCADE CONSTRAINTS;

CREATE TABLE Match 
(
    Heim VARCHAR(50),
    Gast VARCHAR(50),
    HeimP NUMBER,
    GastP NUMBER
);

INSERT INTO Match 
VALUES ('Bayern München', 'Borussia Dortmund', 1, 1);

INSERT INTO Match 
VALUES ('Borussia Dortmund', 'Bayern München', 0, 3);
INSERT INTO Match 
VALUES ('Bayern München', 'Schalke', 3, 0);
INSERT INTO Match 
VALUES ('Schalke', 'Bayern München', 0, 3);

COMMIT;


SELECT SUM(HeimP) AS Heimpoints 
FROM Match 
WHERE Heim = 'Bayern München';

SELECT SUM(GastP) AS Gastpoints 
FROM Match 
WHERE Gast = 'Bayern München';

Answer 1

You can use a CASE expression inside SUM():

SELECT SUM(
         CASE 'Bayern München'
           WHEN Heim THEN HeimP 
           WHEN Gast THEN GastP
         END
       ) AS points 
FROM Match 
WHERE 'Bayern München' IN (Gast, Heim);

See the demo.
Results:

> | POINTS |
> | -----: |
> |     10 |

Answer 2

One way to do this is to replace the conditions in the where clause with conditions in a case expression and only sum the relevant rows:

SELECT SUM(CASE heim WHEN 'Bayern München' THEN heimp END) AS Heimpoints,
       SUM(CASE gast WHEN 'Bayern München' THEN gastp END) AS Gastpoints
FROM   match
WHERE  'Bayern München' IN (heim, gast) -- optimization to not query useless rows

Answer 3

The NUMBER makes me think you are using Oracle. That is a database that supports lateral joins (which are standard SQL but not supported by all databases).

Something like this might be the simplest solution:

select sum(x.points)
from match m cross join lateral
     (select m.heimp as team, m.heimp as points from dual union all
      select m.gast, m.gastp from dual
     ) x
where x.team = 'Bayern München';

This has the advantage over other solutions of only referring to the specific team once (so avoiding typos). Perhaps more importantly, it easily generalizes to all teams:

select x.team sum(x.points)
from match m cross join lateral
     (select m.heimp as team, m.heimp as points from dual union all
      select m.gast, m.gastp from dual
     ) x
group by x.team;

In databases that don't support lateral joins, you can do something quite similar using union all.