SUM()基于与SELECT不同的条件 [英] SUM() based on a different condition to the SELECT

问题描述

您好，有一种方法可以根据与SELECT语句其余部分不同的条件来执行SUM(total_points)，因此我希望对于$ chosentrack中< =的每一行都使用SUM(total_points)?但SELECT语句的其余条件必须满足以下条件.我需要将它们全部归还...........

非常感谢您的帮助.

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

解决方案

您还可以将总和放入case语句中，其中case评估其他条件，然后仅对条件为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  

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

解决方案

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  

这篇关于SUM()基于与SELECT不同的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！