MYSQL QUERY sum from two tables

Question

Hey there I am having a bit of trouble with the following Mysql query, how can I calculate the SUMS(which are calculating points, the 5th and 6th sum in the query) from the team_stats table not the driver_stats table but also with the same condition as now for where team_id = 3. All i need is too swap those calculations from the driver_stats table to the team_stats table, the problem I am having is when I have tryed doing it, the calculations are way to high like it is not doing them just for where team_id = '3' or whatever number.

Any help would be great thanks.

SELECT       t.teamname,
             t.value,
             SUM(IF(qual_pos = '1', 1,0)) AS poles,
             SUM(IF(race_pos <= '3', 1,0)) AS podiums,
             SUM(IF(race_pos = '1', 1,0)) AS victories,
             SUM(overtakes) AS overtakes,
             SUM(CASE
               WHEN s.track_id = (SELECT MAX(track_id) FROM driver_stats) THEN
                 points
               ELSE
                 0
             End) AS lastracepoints,

             # CHANGE THIS SUM AND THE SUM ABOVE TO CALCULATE
             # the points column in the tale named TEAM_STATS
             # but with the same restrictions as now,
             # only where team_id = 3
             SUM(points) AS points 

FROM         driver_stats s
  LEFT JOIN  drivers d
  ON         d.drivers_id = s.drivers_id
  LEFT JOIN  teams t
  ON         d.team_id = t.team_id
WHERE        d.team_id = 3

score 0 · Answer 1 · edited May 23 '17 at 11:55

MySQL can let you use aggregates (sum) without defining the group by clause in special cases, but it is not typical and won't work for you.

Is it possible to use Aggregate function in a Select statment without using Group By clause?

I haven't tested this by try:

SELECT       t.teamname,
             t.value,
             SUM(IF(qual_pos = '1', 1,0)) AS poles,
             SUM(IF(race_pos <= '3', 1,0)) AS podiums,
             SUM(IF(race_pos = '1', 1,0)) AS victories,
             SUM(overtakes) AS overtakes,
             SUM(CASE
               WHEN s.track_id = (SELECT MAX(track_id) FROM driver_stats) THEN
                 points
               ELSE
                 0
             End) AS lastracepoints,
             SUM(points) AS points 

FROM         drivers d
  INNER JOIN  driver_stats s
  ON         d.drivers_id = s.drivers_id
  AND         d.team_id = 3
  LEFT JOIN  teams t
  ON         d.team_id = t.team_id
GROUP BY
    teamname,
    `value`

MYSQL QUERY sum from two tables

1 Answers1