title: Add total of 3 rows for specific id
body: <p>I have three tables:</p>
  
  <p>Students</p>
  
  <pre><code>-------------------------------------------------------------
  studentId        first        last        gender        weight
  -------------------------------------------------------------
  1                John         Doe         m             185
  2                John         Doe2        m             130   
  3                John         Doe3        m             250
  </code></pre>
  
  <p>Lifts</p>
  
  <pre><code>-------------------
  liftId        name        
  -------------------
  1             Bench Press
  2             Power Clean  
  3             Parallel Squat
  4             Deadlift
  5             Shoulder Press
  </code></pre>
  
  <p>StudentLifts</p>
  
  <pre><code>------------------------------------------------
  studentLiftId   studentId     liftId     weight 
  ------------------------------------------------
  1                1            1          185
  2                2            3          130   
  3                3            1          190
  4                1            2          120
  5                2            1          155   
  6                3            2          145
  7                1            1          135
  8                1            1          205   
  9                2            3          200
  10               1            3          150
  11               2            2          110
  12               3            3          250
  </code></pre>
  
  <p>I would like to have four top lists:</p>
  
  <ol>
  <li><p>Bench Press</p></li>
  <li><p>Parallel Squat</p></li>
  <li><p>Power Clean</p></li>
  <li><p>Total of the above 3</p></li>
  </ol>
  
  <p>I can successfully grab a top list for each specific lift using the following query:</p>
  
  <pre><code>SELECT s.studentId, s.first, s.last, s.gender, s.weight, l.name, sl.weight 
  FROM Students s 
  LEFT JOIN (
    SELECT *
    FROM StudentLifts 
    ORDER BY weight DESC
    ) sl ON sl.studentId = s.studentId 
    LEFT JOIN Lifts l ON l.liftId = sl.liftId 
    WHERE l.name = 'Bench Press' 
    AND s.gender = 'm' 
    AND s.weight &gt; 170 
    GROUP BY s.studentId 
    ORDER BY sl.weight DESC
  </code></pre>
  
  <p>However, I am stuck on how to add the highest total of each lift for each student.  How can I first find the highest total for each student in each lift, and then add them up to get a total of all three lifts?</p>
  
  <p><strong>Edit</strong></p>
  
  <p>The result set that I am looking for would be something like:</p>
  
  <pre><code>-------------------------------------------------
  studentId    first    last    weight
  -------------------------------------------------
  3            John     Doe3    585
  1            John     Doe     475
  2            John     Doe2    465
  </code></pre>
  
  <p>I also forgot to mention that I would actually like two lists, one for students above 170 and one for students below 170.  </p>