CODEWITHSUNDEEP
mysqlsql
user970117
user970117

Reputation: 265

How to use the sum query for specific rows

I am having a bit of a problem with my MYSQL query, here I am returning all the results to populate a league table.

The table looks like a bigger version of this

MEMBERS RESULTS TABLE
member_id      1   2   1   2
track_id       1   1   2   2
total_points  234 432 222 234

The SUM(total) is returning the SUM for all the total_points together no matter what the member_id is, when I need them to calculate just for where each member_id row is.

SELECT members.member_id, members.teamname, SUM(total_points)
FROM members, members_leagues, member_results
   WHERE members.member_id = members_leagues.member_id
     AND members_leagues.league_id = '45'
       AND member_results.track_id = '1'
          AND member_results.member_id = members_leagues.member_id
             AND members_leagues.start_race >= member_results.track_id

Upvotes: 3

Views: 4502

Answers (2)

oezi
oezi

Reputation: 51797

just add a group by

GROUP BY members.member_id

Upvotes: 0

Jonny
Jonny

Reputation: 2917

You need a GROUP BY clause

SELECT members.member_id, members.teamname, SUM(total_points)
FROM members, members_leagues, member_results
   WHERE members.member_id = members_leagues.member_id
     AND members_leagues.league_id = '45'
       AND member_results.track_id = '1'
          AND member_results.member_id = members_leagues.member_id
             AND members_leagues.start_race >= member_results.track_id 
GROUP BY members.memberID

Upvotes: 1

Related Questions