如何按周计算和分组项目？

I have the following (MySQL) table called "tweets":

我有以下(MySQL)表称为“推文”:

tweet_id   created_at
---------------------
1          1298027046
2          1298027100
5          1298477008

I want MySQL returning the number of tweets per day of the week; taking the above data it should return:

我希望MySQL返回一周中每天的推文数量;取以上数据应该返回:

Wednesday 1
Friday    2

I now have the following query (which should return the day of the week index, not the full name):

我现在有以下查询(应该返回星期几的索引,而不是全名):

SELECT 
  COUNT(`tweet_id`),
  WEEKDAY(FROM_UNIXTIME(`created_at`))
FROM tweets2 
ORDER BY WEEKDAY(FROM_UNIXTIME(`created_at`))

This however returns:

然而,这返回:

COUNT(`tweet_id`)   WEEKDAY(FROM_UNIXTIME(`created_at`))
7377                4

(There are a total of 7377 tweets in the database). What am I doing wrong?

(数据库中总共有7377条推文)。我究竟做错了什么?

3 个解决方案

#1

SELECT 
COUNT(`tweet_id`),
DAYNAME(FROM_UNIXTIME(created_at)) AS Day_Name1
FROM tweets2 

GROUP BY Day_Name1
ORDER BY Day_Name1;

#2

You have a count, but you don't have a group by. You should include a

你有一个计数,但你没有一个组。你应该包括一个

GROUP BY WEEKDAY(FROM_UNIXTIME(`created_at`))

#3

You are not grouping by week day so you only get one grand total. Try this:

您没有按工作日进行分组,因此您只能获得一个总计。尝试这个:

SELECT 
  COUNT(`tweet_id`),
  WEEKDAY(FROM_UNIXTIME(`created_at`))
FROM tweets2 
GROUP BY WEEKDAY(FROM_UNIXTIME(`created_at`))
ORDER BY WEEKDAY(FROM_UNIXTIME(`created_at`));

#1

SELECT 
COUNT(`tweet_id`),
DAYNAME(FROM_UNIXTIME(created_at)) AS Day_Name1
FROM tweets2 

GROUP BY Day_Name1
ORDER BY Day_Name1;

#2