CODEWITHSUNDEEP
mysqltime
john
john

Reputation: 1280

Fetching weekly, monthly, yearly data in one query

I have a page displaying users statistics and I'm wondering what the best way to get weekly, monthly, yearly and all time stats from a table is in one query?

At the moment I'm using separate queries to check for results within each date range but there has to be a better way. For example purposes, I'd like to count all rows there 'votes' = '1' in the past month, week, and year.

Upvotes: 0

Views: 4171

Answers (1)

nosid
nosid

Reputation: 50044

SELECT
   user_id,
   SUM(IF(ts>CURRENT_DATE - INTERVAL 1 WEEK,votes,0)) votes_weekly,
   SUM(IF(ts>CURRENT_DATE - INTERVAL 1 MONTH,votes,0)) votes_monthly,
   SUM(IF(ts>CURRENT_DATE - INTERVAL 1 YEAR,votes,0)) votes_yearly
FROM table
WHERE ts>CURRENT_DATE - INTERVAL 1 YEAR
GROUP BY user_id;

Upvotes: 5

Related Questions