title: sql - Get count of matching rows from multiple joined tables
body: <p>I am trying to get the count of clicks and views belonging to individual rows in my "ads" table (which also belongs to a user).  I am also trying to order the result by the ads with the highest number of views.</p>
  
  <p>My tables structure:</p>
  
  <p>users:</p>
  
  <pre><code>id name
  1  James
  </code></pre>
  
  <p>ads: belongs to users (FK: user_id) </p>
  
  <pre><code>id user_id
  22 1
  23 4
  </code></pre>
  
  <p>views: belongs to ads (FK: ad_id)</p>
  
  <pre><code>id ad_id
  1  22
  2  22
  3  23
  </code></pre>
  
  <p>clicks: belongs to ads (FK: ad_id)</p>
  
  <pre><code>id ad_id
  1  22
  2  22
  3  23
  </code></pre>
  
  <p>From the above tables, I want a result that contains data about all ads belonging to user with id = 1 and the click and view count for each ad</p>
  
  <pre><code>id user_id clicks_count views_count
  22 1       2            2
  </code></pre>
  
  <p>This is what I have so far. With this SQL, I get the correct number of views and clicks for just one/first "ads" row but other matching rows are ignored.</p>
  
  <pre><code>SELECT ads.id, COUNT(DISTINCT views.id) AS view_count, COUNT(DISTINCT clicks.id) AS click_count   
  FROM users
  INNER JOIN ads ON ads.`user_id` = users.`id`
  INNER JOIN views ON ads.id = views.`ad_id`
  INNER JOIN clicks ON ads.id = clicks.`ad_id`
  WHERE users.`id` = 1
  GROUP BY views.`ad_id`
  ORDER BY `view_count` DESC
  </code></pre>
  
  <p>Here is an sqlfiddle <a href="http://sqlfiddle.com/#!2/5c43c/1/0" rel="nofollow">http://sqlfiddle.com/#!2/5c43c/1/0</a></p>
  
  <p>Thanks in advance</p>