SUM () based on another condition for SELECT

Question

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

Thanks so much 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 

Answer 1

You can also put the sum inside the case-case, where the case evaluates another condition, and then writes only the sums of the 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 

Answer 2

 SELECT ... SUM(CASE WHEN track_id <= [your_value] THEN total_points ELSE 0 END ) AS total_points, .... 

Answer 3

Make the amount as a subheading, making sure that you select an additional column (do not forget to group by this column) and attach the result of this subquery to the main query using a 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 ....