SELECT COUNT(`tweet_id`),DAYNAME(FROM_UNIXTIME(created_at)) AS Day_Name1FROM tweets2 GROUP BY Day_Name1ORDER BY Day_Name1;
You have a count, but you don't have a group by. You should include a
GROUP BY WEEKDAY(FROM_UNIXTIME(`created_at`))
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`));