CODEWITHSUNDEEP
sqlsum
Ryoss
Ryoss

Reputation: 59

How to add the result of two sums?

Is it possible to add the result of two Sum() functions?

The Table:

  DROP TABLE MATCH;
  CREATE TABLE MATCH 
   (M_ID VARCHAR2 (4) NOT NULL,
    HT_ID VARCHAR2 (4),
    AT_ID VARCHAR2 (4),
    P_F NUMBER (3),
  P_A NUMBER (3));

   INSERT INTO MATCH VALUES
   ('M01','T1', 'T2',  2, 0);

  INSERT INTO MATCH VALUES
  ('M02','T1', 'T2',  1, 1);

  INSERT INTO MATCH VALUES
  ('M03','T1', 'T2',  0, 2);

  INSERT INTO MATCH VALUES
  ('M04','T1', 'T2', 0, 2);

  INSERT INTO MATCH VALUES
  ('M05','T2', 'T1', 2, 0);

  INSERT INTO MATCH VALUES
  ('M06','T2', 'T1',  0, 2);

  INSERT INTO MATCH VALUES
  ('M07','T2', 'T1',  1, 2);

  INSERT INTO MATCH VALUES
  ('M08','T2', 'T1',  0, 2);

What I'm trying to receive is the points (points for (P_F) and points against(P_A)) of the home team "T1".

To make it slightly more clearer, I do the following of which yields a total of 9.

 select sum(P_F)
 from match
 where HT_ID='T1';

 select sum(P_A)
 from match
 where AT_ID='T1';

I tried the following but it adds both columns for that particular team, resulting 17:

 select sum(P_F+P_A)
 from match
 where ht_id='T1' or at_id='T1';

How could I add the two sums in one query? Thanks in advance.

Upvotes: 2

Views: 132

Answers (2)

GarethD
GarethD

Reputation: 69809

You can use CASE inside your sum:

SELECT  SUM(CASE WHEN ht_ID = 'T1' THEN P_F ELSE 0 END + CASE WHEN AT_ID = 'T1' THEN P_A ELSE 0 END)
FROM    Match
WHERE   ht_id='T1' 
OR      at_id='T1';

Example on SQL Fiddle

Upvotes: 2

YXD
YXD

Reputation: 32521

select
sum(case when HT='T1' then P_F else 0 end), 
sum(case when AT='T1' then P_A else 0 end)
from match

to add:

select
sum(case when HT='T1' then P_F else 0 end) +
sum(case when AT='T1' then P_A else 0 end)
from match

Upvotes: 4

Related Questions