SQL Query to find top 5 cumulative tweets

How to find the top 5 months with the highest number of cumulative tweets and sort it according to the number of tweets of each month.

Table twitter structure is like this:

Token type  Month   count   Hash Tag Name
hashtag     200910  2   Babylove
hashtag     200911  2   babylove
hashtag     200912  90  babylove
hashtag     200812  100 mycoolwife
hashtag     200901  201 mycoolwife
hashtag     200910  1   mycoolwife
hashtag     200912  500 mycoolwife
hashtag     200905  23  abc
hashtag     200907  1000 abc

​x
 
Token type  Month   count   Hash Tag Namehashtag     200910  2   Babylovehashtag     200911  2   babylovehashtag     200912  90  babylovehashtag     200812  100 mycoolwifehashtag     200901  201 mycoolwifehashtag     200910  1   mycoolwifehashtag     200912  500 mycoolwifehashtag     200905  23  abchashtag     200907  1000 abc​

Output should be like this

 month  numtweets
200907  1000
200912  590
200901  201
200812  100
200905  23

 
 month  numtweets200907  1000200912  590200901  201200812  100200905  23​

Answer

Is this what you need ?

SELECT Month,SUM(count) as numtweets
FROM twitter 
GROUP BY Month
ORDER BY numtweets DESC
LIMIT 5

 
SELECT Month,SUM(count) as numtweetsFROM twitter GROUP BY MonthORDER BY numtweets DESCLIMIT 5​

Advertisement

Answer