CODEWITHSUNDEEP
phpmysqldatabase
Jazerix
Jazerix

Reputation: 4801

Get total from a month and limit results MySQL

I need to make a query that returns the top ten from a month. I hope this is possible directly through MySQL.

Basically I get some data every day (around 150 rows), with some information about different youtubers. Each row has an Identifier, a Date, the YouTuber, and the number of views the youtuber got that day. It looks like this:

ID|Date|Youtuber|Views
126|2013-01-28|example|482612

I need to sum all of the views and to a total, for a month. For example May, and then i need to get the results that has most views for that month.

How would I do that through mysql? Or would I need to use php as well? :)

Cheers

Upvotes: 0

Views: 345

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562310

I see two these as two queries:

  1. Sum all of the views for a month. For example May. 

    SELECT SUM(Views) as TotalViews 
FROM MyTable
WHERE Date >= '2013-05-01' AND Date < '2013-06-01';

  2. Get the results that has most views for that month.

    SELECT t.YouTuber, t.Date, t.Views
From MyTable AS t
JOIN (
    SELECT YouTuber, MAX(Views) AS Views
    FROM MyTable
    WHERE Date >= '2013-05-01' AND Date < '2013-06-01'
    GROUP BY YouTuber
  ) AS max USING (YouTuber, Views) 
WHERE Date >= '2013-05-01' AND Date < '2013-06-01';

Upvotes: 0

RandomSeed
RandomSeed

Reputation: 29759

SELECT youtuber, SUM(views) AS total_views
FROM yourtable
WHERE date >= '2013-05-01' AND date < '2013-06-01'
GROUP BY youtuber
ORDER BY total_views DESC
LIMIT 10;

Upvotes: 0

Schleis
Schleis

Reputation: 43700

SELECT Youtuber, SUM(Views) as viewTotal FROM <TABLE> WHERE month(Date) = 5 GROUP BY Youtuber ORDER BY viewTotal LIMIT 0,10

Upvotes: 0

Related Questions