CODEWITHSUNDEEP
mysqlsum
dsol828
dsol828

Reputation: 413

Sum values across multiple tables

I'm having trouble trying to sum a field GROUPED BY a common user ID from TWO DIFFERENT tables.

To give you a little more info... I am trying to track player performances by date (i.e.: most points scored on ALL Nov. 14's). The database is split, one table for regular season games and one table for playoffs. So, for example, a player may have played a regular season game on May 3, 2001....but a playoff game on May 3, 2005.

So, I'm trying to build a view with the sums of every player on all dates.

What I have for a single table:

SELECT PlayerId,sum(Points) as TOT_PTS 
FROM RS_games 
WHERE DAY(Date)=$cur_day 
  AND MONTH(Date)=$cur_month 
GROUP BY PlayerId

...but I can't figure how I could sum the values of each player across two tables without creating a third view as a "stepping stone". Any ideas?

Upvotes: 1

Views: 137

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

If you want the results by (DAY-MONTH) you can do:

SELECT playerID, 
       CONCAT (DAY(DATE), '-', MONTH(DATE)) AS DAY_MONTH, 
       SUM(points) AS Total_Points
FROM (
  SELECT playerID, DATE, points
  FROM rs_games  
  UNION ALL  
  SELECT playerID, DATE, points
  FROM po_games
  ) a
GROUP BY 1, 2

sqlfiddle demo

This way, you would end up with a result with every playerId,dd-mm, and the sum of points that were scored in that specific day across the years.

Upvotes: 1

user645280
user645280

Reputation:

Just to lay out what I was saying: 

select
  ALL_players.PlayerID as PlayerID
  COALESCE(reg.Points, 0) + COALESCE(po.Points, 0) as Points
from
ALL_players
left join
(select PlayerID, DATE(Date) as Date, sum(Points) as Points 
 from RS_games
 WHERE DAY(Date)=$cur_day AND MONTH(Date)=$cur_month
 group by PlayerID) as reg
on reg.PlayerID = ALL_players.PlayerID
left join
(select PlayerID, DATE(Date) as Date, sum(Points) as Points 
 from PO_games group by DATE(Date), PlayerID
 WHERE DAY(Date)=$cur_day AND MONTH(Date)=$cur_month
 group by PlayerID) as po
on po.PlayerID = ALL_players.PlayerID

EDIT: Looking again at requirements this will need either a full outer join or some adjustment... adjusting (should be working now)

Upvotes: 0

Related Questions