How to count and group items by day of the week?

Question

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

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

I want MySQL to return the number of tweets per day of the week; taking the above data, he should return:

 Wednesday 1 Friday 2 

Now I have the following query (which should return 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 returns:

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

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

Answer 1

 SELECT COUNT(`tweet_id`), DAYNAME(FROM_UNIXTIME(created_at)) AS Day_Name1 FROM tweets2 GROUP BY Day_Name1 ORDER BY Day_Name1; 

Answer 2

You have an account, but you do not have a group. You must enable

 GROUP BY WEEKDAY(FROM_UNIXTIME(`created_at`)) 

Answer 3

You do not group on weekdays, so you get only one total amount. Try the following:

 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`));