mysql查每天的销售额,计算MySQL每天的平均销售额

In my database I have a table with two columns. The first column contains dates and the second is a count variable. I was wondering if it is possible to compute the average counts for each weekday based on the dates and counts.

In the following a small example:

Table:

Date Count

02/01/2005 100

02/02/2005 200

02/03/2005 300

... ...

Output:

Days Average

Monday 120.5

Tuesday 200.2

Wednesday 300.5

解决方案

You could a series of avg calls on case expression extracting the day's name:

SELECT AVG(CASE DAYOFWEEK(`date`) WHEN 2 THEN `count` ELSE NULL END) AS Monday,

AVG(CASE DAYOFWEEK(`date`) WHEN 3 THEN `count` ELSE NULL END) AS Tuesday,

AVG(CASE DAYOFWEEK(`date`) WHEN 4 THEN `count` ELSE NULL END) AS Wednesday,

AVG(CASE DAYOFWEEK(`date`) WHEN 5 THEN `count` ELSE NULL END) AS Thursday,

AVG(CASE DAYOFWEEK(`date`) WHEN 6 THEN `count` ELSE NULL END) AS Friday

FROM mytable

EDIT:

Given the updated expected output in the edited post, it's much easier to do - just group by the dayname:

SELECT DAYNAME(`date`), AVG(`count`)

FROM mytable

WHERE DAYOFWEEK(`date`) BETWEEN 2 AND 6

GROUP BY DAYNAME(`date`)