mysql distinct value for count

Question

I am all over the place here. The MySql query below works for me except for two issues.

1) I want to make sure that only one result per category is displayed (I have 5 categories), I tried using DISTINCT but have clearly misunderstood that one.

2) I want to be able to return the howmany value outside the sql query:

"SELECT DISTINCT category_id, video_id, date_added, COUNT(video_id) AS howmany
FROM votes 
WHERE date_added BETWEEN SYSDATE() - INTERVAL 7 DAY AND SYSDATE()
GROUP BY video_id 
ORDER BY howmany DESC, video_id
LIMIT 5";

my votes table looks like this:

id | video_id | category_id | date_added

EDIT: Expected output -

video_id 2 category_id 4 number of votes 500

video_id 5 category_id 1 number of votes 377

video_id 88 category_id 3 number of votes 25

video_id 45 category_id 5 number of votes 23

video_id 9 category_id 2 number of votes 2

The highest voted video in each category over the last 7 days is displayed.

Answer 1

Most likely you'd want to GROUP BY category_id instead, or even GROUP BY category_id, video_id if you want to break the results down further.

Answer 2

If you want only 1 result per category, you have to group by category. But since there could be multiple video_id's per category, you would end up getting a random one from within the category unless you use GROUP_CONCAT.

If you just want to get a distinct count of a column value, put the DISTINCT keyword inside the count function:

COUNT( DISTINCT video_id )

Answer 3

SELECT DISTINCT category_id, video_id, date_added, COUNT(video_id) AS howmany

is functionally equivalent to

GROUP BY category_id, video_id, date_added, COUNT(video_id)

You said "I want to make sure that only one result per category is displayed", so you probably want something like this:

SELECT category_id, video_id, date_added, COUNT(video_id) AS howmany
FROM votes 
WHERE date_added BETWEEN SYSDATE() - INTERVAL 7 DAY AND SYSDATE()
GROUP BY category_id 
ORDER BY howmany DESC, video_id
LIMIT 5;

Answer 4

MySQL doesn't have the analytic functionality you'd use for situations like these - use:

SELECT x.video_id,
       x.category_id,
       x.howmany
  FROM (SELECT t.video_id,
               t.category_id,
               COUNT(*) AS howmany,
               CASE 
                 WHEN @category = t.category_id THEN @rownum 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.category_id
          FROM VOTES t
          JOIN (SELECT @rownum := 0, @category := -1) r
         WHERE t.date_added BETWEEN SYSDATE() - INTERVAL 7 DAY AND SYSDATE()
      GROUP BY t.video_id, t.category_id
     ORDER BY t.category_id, howmany DESC) x
 WHERE x.rank = 1
ORDER BY x.howmany DESC
   LIMIT 5