计算日期范围内的天数总计

Question

I want to count totals for all mondays, tuesdays, etc. for any given (user selected) date range, StartDate (sDate) through EndDate (eDate). My current query shows all days within range and I'd like it to SUM my table "count" from each day as it is but display total of each day of week as one occurrence. Using image sample, I want my result to be: Monday - 164 Tuesday - 139 Wednesday - 261 etc.

SQL

SELECT areaname,created, SUM(count) as totals FROM reports WHERE created 
between '".$sDate."'  AND  '".$eDate."' AND area = '".$tArea."' GROUP BY areaname, 
created ORDER BY id ASC

html

foreach ($counts as $row) { ?>
<tr><td><?php echo date('l',strtotime($row['reports']['created'])); ?></td>
<?php echo $row[0]['totals']; ?>
}

Answer 1

You can use the DATE_FORMAT() function to get the day of the week from a given date, then you can group by that. The syntax is DATE_FORMAT(date, '%W'). So:

SELECT areaname,
  DATE_FORMAT(created, '%W') AS weekday,
  SUM(count) AS totals
FROM reports
WHERE created BETWEEN '".$sDate."' AND '".$eDate."'
  AND area = '".$tArea."'
GROUP BY areaname, weekday
ORDER BY id

Note that I left in areaname in the GROUP BY, so this will return one result for every combination of areaname/weekday. If you want totals for all areanames, just remove areaname from the GROUP BY clause.