SQL SELECT SUM from two tables and GROUP BY date

Question

I have two tables (rescues and counts) with one to many relationship in a MySQL 5.1 database. There could be multiple rows in rescues with the same datetime. In counts there is at least one row (ore more) associated to rescues (rescues.id = counts.id_rescue).

I want to get:

• the sum of a column in "rescues"
• the sum of some columns in "counts"
• the sum of a column in "counts"
• group the results by date

This is what I am doing: http://sqlfiddle.com/#!8/9a290/2/0

I get these results (wrong sum_volounteers but correct sum_alive and sum_dead):

date        sum_volounteers   sum_alive   sum_dead
2014-02-02  5                 138         18
2014-02-04  4                 53          15

What I am expecting is:

date        sum_volounteers   sum_alive   sum_dead
2014-02-02  11                138         18
2014-02-04  5                 53          15

Answer 1

The solution is to select SUM(r.volounteers) AS sum_volounteers in the outer query, then in the subquery get sum_alive, sum_dead and date_of_sums (using a JOIN in the subquery) and GROUP BY date_of_sums then JOIN c.date_of_sums = CAST(r.start AS DATE)

The other query I posted fails when a record is added in rescues with the same number in the column rescues.volounteers, this is the correct one:

SELECT
   r.id,
   CAST(r.start AS DATE) AS date,
   SUM(r.volounteers) AS sum_volounteers,
   sum_alive,
   sum_dead
FROM rescues AS r
JOIN(
   SELECT
   CAST(r2.start AS DATE) AS date_of_sums,
   sum(c2.males+c2.females+c2.uncertains+c2.couples*2) AS sum_alive,
   sum(roadkills) AS sum_dead
   FROM counts AS c2
   JOIN rescues as r2
   ON r2.id = c2.id_rescue
   GROUP BY date_of_sums
  ) AS c
ON c.date_of_sums = CAST(r.start AS DATE)
WHERE
   CAST(r.start AS DATE) >= '2014-02-02'
   AND CAST(r.start AS DATE) <= '2014-02-04'
GROUP BY CAST(r.start AS DATE)

Here's a demo:

• correct query: http://sqlfiddle.com/#!8/cc1eb/77
• wrong (old) query: http://sqlfiddle.com/#!8/efd26/1/0

This query gives the correct result:

date        sum_volounteers   sum_alive   sum_dead
2014-02-02  11                138         18
2014-02-04  9                 73          23

Answer 2

Edit: I found a problem using this query, if there are two ore more rows in rescues with the same number in the column rescues.volounteers the results are wrong. See my other answer for the correct solution.

Thanks to other comments I figured out that I needed to SUM(DISTINCT r.volounteers) on the outer query: http://sqlfiddle.com/#!8/9a290/69/0

Using SUM() in the subquery is not required.

SELECT
r.date,
sum(DISTINCT r.volounteers) AS sum_volounteers,
sum(c.males + c.females + c.uncertains + c.couples*2) AS sum_alive,
sum(c.roadkills) AS sum_dead
FROM counts AS c
JOIN(
  SELECT
  r2.id AS selected_id,
  r2.volounteers AS volounteers,
  CAST(r2.start AS DATE) AS date
  FROM rescues AS r2
  GROUP BY selected_id
) AS r ON c.id_rescue = r.selected_id
WHERE r.date >= '2014-02-02' AND r.date <= '2014-02-04'
GROUP BY r.date

Now I'm getting correct results:

date        sum_volounteers   sum_alive   sum_dead
2014-02-02  11                138         18
2014-02-04  5                 53          15

Answer 3

I believe you just need to SUM() the volunteers on the outer query:

SELECT
r.date,
sum(r.sum_volounteers) AS sum_Volunteers,
sum(c.males + c.females + c.uncertains + c.couples*2) AS sum_alive,
sum(c.roadkills) AS sum_dead
FROM counts AS c
JOIN(
  SELECT
  r2.id AS selected_id,
  sum(r2.volounteers) AS sum_volounteers,
  CAST(r2.start AS DATE) AS date
  FROM rescues AS r2
  GROUP BY selected_id
) AS r ON c.id_rescue = r.selected_id
WHERE r.date >= '2014-02-02' AND r.date <= '2014-02-04'
GROUP BY r.date

Demo: SQL Fiddle

Since you were only grouping on the date, MySQL was just picking one of the sum_volunteers values to return from your subquery, while there were multiple for that date.