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

Question

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

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:

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?

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 a count, but you don't have a group by. You should include a

GROUP BY WEEKDAY(FROM_UNIXTIME(`created_at`))