CODEWITHSUNDEEP
sqlite
Stewart
Stewart

Reputation: 5062

SQLite Sum of Sums

Let's say I have two tables which look like this: 

Games:
| AwayTeam  | HomeTeam     | AwayPoints | HomePoints |
------------------------------------------------------
| Aardvarks | Bobcats      | 2          | 1          |
| Bobcats   | Caterpillars | 20         | 10         |
| Aardvarks | Caterpillars | 200        | 100        |

Teams:
| Name         | 
----------------
| Aardvarks    | 
| Bobcats      |
| Caterpillars |

How can I make a result which looks like this? 

| Name         | TotalPoints |
------------------------------
| Aardvarks    | 202         |
| Bobcats      |  21         | 
| Caterpillars | 110         |

I think my real problem is how to splice statements together in SQL. These two statements work well individually:

SELECT SUM ( AwayPoints ) 
FROM   Games 
WHERE  AwayTeam='Bobcats';

SELECT SUM ( HomePoints ) 
FROM   Games 
WHERE  HomeTeam='Bobcats';

I suspect that I need a compound operator if I want to splice two SELECT statements togeather. Then pass that statement into the aggregate expression below:

SELECT   Name, SUM( aggregate_expression ) 
AS       'TotalPoints' 
FROM     Teams 
GROUP BY Name;

If I had to just throw it all together, I think I'd end up with something like this: 

SELECT   Name, SUM (
    SELECT SUM ( AwayPoints ) 
    FROM   Games 
    WHERE  AwayTeam=Name 
  UNION 
    SELECT SUM ( HomePoints )
    FROM   Games 
    WHERE  HomeTeam=Name
)
AS       'TotalPoints' 
FROM     Teams 
GROUP BY Name;

However that doesn't work because SELECT SUM ( SELECT ... is completely invalid

Upvotes: 1

Views: 312

Answers (1)

Matt
Matt

Reputation: 15061

Use a UNION ALL

SELECT team, SUM(points)
FROM (
  SELECT HomeTeam AS team, SUM(HomePoints) AS points
  FROM Games
  GROUP BY HomeTeam
 UNION ALL
  SELECT AwayTeam AS team, SUM(AwayPoints) AS points
  FROM Games
  GROUP BY AwayTeam
)
GROUP BY team

See SQLite documentation for

Upvotes: 1

Related Questions