title: mysql distinct value for count
body: <p>I am all over the place here. The MySql query below works for me except for two issues.</p>
  
  <p>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.</p>
  
  <p>2) I want to be able to return the howmany value outside the sql query:</p>
  
  <pre><code>"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";
  </code></pre>
  
  <p>my votes table looks like this:</p>
  
  <pre><code>id | video_id | category_id | date_added
  </code></pre>
  
  <p>EDIT:
  Expected output - </p>
  
  <p>video_id 2 category_id 4 number of votes 500</p>
  
  <p>video_id 5 category_id 1 number of votes 377</p>
  
  <p>video_id 88 category_id 3 number of votes 25</p>
  
  <p>video_id 45 category_id 5 number of votes 23</p>
  
  <p>video_id 9 category_id 2 number of votes 2</p>
  
  <p>The highest voted video in each category over the last 7 days is displayed.</p>