CODEWITHSUNDEEP
sql
deltanovember
deltanovember

Reputation: 44051

How do I perform the following SQL sum?

Suppose we have the following table

match_id, user_id, score

1000, 1, 359
1000, 52, 290
1001, 1, 429
1001, 59, 374

Basically in match_id 1000, user_id 1 wins 359-290, in match_id 1001, user_id 1 wins 429-374

To work out the total points user 1 is easy

select user_id,sum(score) as pointsfor group by user_id

How do I determine how many points were scored against? Basically what should X in the following SQL be?

select user_id,sum(score) as pointsfor,sum(X) as pointsagainst  group by user_id

Upvotes: 0

Views: 129

Answers (3)

isobar
isobar

Reputation: 1175

You can give this a try

select user_id, sum(score) as pointsfor,
    (select sum(score) 
      from tab b 
      where a.match_id=b.match_id and a.user_id !=b.user_id) as pointsagainst 
from tab a
group by user_id

Upvotes: 1

mrcrowl
mrcrowl

Reputation: 1479

The only way to know whether the score is for or against is to compare the scores.

I'd use a subquery to first determine the points, and then sum up per user: (this assumes your table name is "Points"):

select user_id, SUM(pointsfor) as PointsFor, SUM(pointsagainst) as PointsAgainst, SUM(pointstied) as PointsTied
from (
    select a.user_id, case when a.score > b.score then a.score else 0 end as PointsFor,
    case when a.score < b.score then a.score else 0 end as PointsAgainst,
    case when a.score = b.score then a.score else 0 end as PointsTied
    from points a
    join Points b on a.match_id = b.match_id and a.user_id != b.user_id
) sub
group by sub.user_id

Upvotes: 0

tiago2014
tiago2014

Reputation: 3461

I can't test right now, but try something like:

SELECT
  PointsFor.user_id, 
  sum(PointsFor.score) as pointsfor,
  sum(PointsAgainst.score) as pointsAgainst
FROM TABLE_NAME PointsFor
JOIN TABLE_NAME PointsAgainst
  on PointsFor.match_id = PointsAgainst.match_id
  and PointsFor.user_id <> PointsAgainst.user_id
GROUP BY PointsFor.user_id

Obs: Replace TABLE_NAME above with the actual name of your table.

Upvotes: 2

Related Questions