SQL SELECT SUM из двух таблиц и даты GROUP BY

У меня есть две таблицы (rescues и counts) с отношением один к многим в базе данных MySQL 5.1. Там может быть несколько строк в rescues с тем же временем datetime. В counts есть по крайней мере одна строка (больше), связанная с rescues (rescues.id = counts.id_rescue).

Я хочу получить:

  • сумма столбца в "спасет"
  • сумма некоторых столбцов в "counts"
  • сумма столбца в "counts"
  • группировать результаты по дате

Это то, что я делаю: http://sqlfiddle.com/#!8/9a290/2/0

Я получаю эти результаты (неверные sum_volounteers но правильно sum_alive и sum_dead):

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

Я ожидаю:

date sum_volounteers sum_alive sum_dead
2014-02-02 11 138 18
2014-02-04 5 53 15
3 ответа

Я считаю, что вам просто нужно SUM() добровольцев по внешнему запросу:

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

Демо: SQL Fiddle

Поскольку вы только группировались в дату, MySQL просто выбирал одно из значений sum_volunteers чтобы вернуться из вашего подзапроса, в то время как для этой даты было несколько.


Изменение: Я нашел проблему, используя этот запрос, если есть руда еще два ряда в rescues с тем же номером в столбце rescues.volounteers результатов неверны. См. Мой другой ответ для правильного решения.

Благодаря другим комментариям я выяснил, что мне нужен SUM(DISTINCT r.volounteers) по внешнему запросу: http://sqlfiddle.com/#!8/9a290/69/0

Использование SUM() в подзапросе не требуется.

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

Теперь я получаю правильные результаты:

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


Решение состоит в том, чтобы выбрать SUM(r.volounteers) AS sum_volounteers во внешнем запросе, затем в подзапросе get sum_alive, sum_dead и date_of_sums (используя JOIN в подзапросе) и GROUP BY date_of_sums затем JOIN c.date_of_sums = CAST(r.start AS DATE)

Другой запрос, который я отправил, не удался, когда запись добавлена в rescues с тем же номером в столбце rescues.volounteers, это правильный:

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)

Вот демо:

Этот запрос дает правильный результат:

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

licensed under cc by-sa 3.0 with attribution.