我需要为员工属于一个部门的每一天选择一个数据总和,我希望能够选择一个日期范围并拥有每天的摘要,这是可能的,目前我只是在一个字段中获得整个日期范围的摘要。
这是我目前的查询
select
date,
SUM(no_of_live_products)
from
fulfilment
INNER JOIN
employee
ON employee.employee_id = fulfilment.employee_id
where
employee.department = 'Telecoms Services'
and date >= '2013-09-16'
and date <= '2013-09-29'
是否可以获取该日期范围内每天的数据摘要?
你可以
GROUP BY `date`
查看每天的总和
如果要获取天数并且有多个时间范围,
首先,获取单个时间范围的日期数,然后将这些日期数相加(需要子选择(。
以这个为例,这不是针对您的情况的完整查询:
SELECT SUM(x.days)
FROM (
SELECT TIMESTAMPDIFF(DAY, startDate, endDate) AS days
FROM fulfilment
) x
GROUP BY employee.employee_id
为什么不试试
SELECT
date,
SUM(no_of_live_products)
FROM
fulfilment
INNER JOIN
employee ON employee.employee_id = fulfilment.employee_id
WHERE
employee.department = 'Telecoms Services'
AND date >= '2013-09-16'
AND date <= '2013-09-29'
GROUP BY date(date)