14

Hi is there a way that I can do the SUM(total_points) based on a different condition to the rest of the SELECT statement, so I want the SUM(total_points) for every row which is <= to $chosentrack? but the rest of the conditions of the SELECT statement to be what they are below. I need them to all be returned together..as I am populating a league table.

Thanks a lot for any help.

SELECT
    members.member_id,
    members.teamname,
    SUM(total_points) as total_points,
    total_points as last_race_points 
FROM
    members,
    members_leagues,
    member_results 
WHERE
    members.member_id = members_leagues.member_id 
    AND members_leagues.league_id = '$chosenleague' 
    AND member_results.track_id = '$chosentrack' 
    AND members_leagues.start_race = '$chosentrack' 
    AND member_results.member_id = members_leagues.member_id
GROUP BY
    members.member_id 
ORDER BY
    member_results.total_points DESC,
    last_race_points DESC, 
    members.teamname DESC
user970117
  • 245
  • 1
  • 3
  • 9

3 Answers3

42

You can also put the sum inside a case statement, where the case evaluates the other condition, and then only sum thoses records where the condition is true...

  SELECT m.member_id, m.teamname, 
    Sum(Case When r.track_Id = '$chosentrack' 
         Then total_points Else 0 End) TotalChosenTrackPoints,
    Sum(Case When r.track_Id < '$chosentrack' 
         Then total_points Else 0 End) TotalLessThanChosenTrackPoints, 
    total_points as last_race_points  
 FROM members m
    Join members_leagues l
       On l.member_id = m.member_id  
    Join member_results r
       On r.member_id = m.member_id
 Where l.league_id = '$chosenleague'
    And l.start_race = '$chosentrack'
 Group By m.member_id
 Order By r.total_points Desc, 
     last_race_points  Desc, m.TeamName Desc
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • 1
    but its still not calculating the total_points for all the rows where track_id in the members_results table is less then $chosentrack? its only calculating the total_points for where its equal to $chosentrack? – user970117 Oct 05 '11 at 00:25
  • As [edited] it should be calculating the total poiints for all the rows where member_results.trackId is equal to $chosentrack, AND the total points for all the rows where member_results.trackId is less than $chosentrack. I had to remove the condition in the where clause... – Charles Bretana Oct 05 '11 at 00:35
  • thanks mate! you pointed me in the right direction! :-) – funder7 Dec 06 '20 at 20:59
8
SELECT ... 
SUM(CASE
WHEN track_id <= [your_value] THEN total_points
ELSE 0
END
) AS total_points, ....
a1ex07
  • 36,826
  • 12
  • 90
  • 103
2

Do the sum as a subselect, making sure that you select an additional column (don't forget to group by that column) and join the result of this subselect to the main query using the common field.

Template:

Select col1, 
col2 as t,x.thesum
From table t left join 
( select sum(colx) as thesum, col1 from t where ...
 Group by col1) x on t.col1=x.col1
 Where ....
Icarus
  • 63,293
  • 14
  • 100
  • 115
  • sorry so I want the SUM(total_points) for every row which is <= to $chosentrack, which is the same variable used in the rest of the statement, will your answer still apply to this??? – user970117 Oct 05 '11 at 00:00
  • 1
    Yes, it applies, and it should work. this is just one way to do it. At first I didn't realize that you were selecting columns from the same table and applying a single condition so the 'case' may be more readable and perhaps easier to optimize by the RDBMS. – Icarus Oct 05 '11 at 00:46