mysql从子查询中获取列的总和，同时仍然返回所有记录

Question

我正在尝试返回一组记录，但也想包括总和。

SELECT track_date AS label, track_count AS value
FROM trackers
INNER JOIN (
SELECT tracker_details.track_id, COUNT(track_id) AS track_count, DATE(date_created) AS track_date
    FROM tracker_details
    WHERE (DATE(date_created) >= '2018-01-21' 
    AND DATE(date_created) <= '2018-01-27') 
    AND client_id = 1
    GROUP BY DATE(date_created)) as tracker_details 
ON trackers.track_id = tracker_details.track_id
WHERE trackers.client_id = 1 
ORDER BY DATE(track_date)

当前没有总和的返回:

label       value
2018-01-21  9
2018-01-22  2
2018-01-26  3
2018-01-27  2

通过在选择中添加 SUM:

SELECT track_date AS label, track_count as value, SUM(track_count) as total

它正在返回:

label   value   total
2018-01-22  2   16

我想要实现的是:

label       value total
2018-01-21  9     16
2018-01-22  2     16
2018-01-26  3     16
2018-01-27  2     16

我错过了什么？感谢您的帮助。

提前谢谢你。

编辑我能够通过添加 WITH ROLLUP 来解决这个问题。'按日期分组(date_created)带 ROLLUP'

这创建了一个额外的行:

label       value
null        21
2018-01-15  2
2018-01-16  5
2018-01-17  1
2018-01-20  4
2018-01-21  9

Answer 1

不幸的是，SUM() 和其他聚合函数只返回一行。您需要在执行查询后以编程方式添加列，或者将总和添加为子查询，如下所示:

SELECT track_date AS label, track_count AS value,
  (SELECT count(track_id) AS track_sum
   FROM tracker_details
   WHERE (DATE(date_created) >= '2018-01-21' 
          AND DATE(date_created) <= '2018-01-27') 
   AND client_id = 1) as total
FROM trackers
INNER JOIN (
SELECT tracker_details.track_id, COUNT(track_id) AS track_count, DATE(date_created) AS track_date
    FROM tracker_details
    WHERE (DATE(date_created) >= '2018-01-21' 
    AND DATE(date_created) <= '2018-01-27') 
    AND client_id = 1
    GROUP BY DATE(date_created)) as tracker_details 
ON trackers.track_id = tracker_details.track_id
WHERE trackers.client_id = 1 
ORDER BY DATE(track_date)