title: Sql count with inner join and distinct values
body: <p>i'm currently working on a query and I want to return rows from multiple tables and these need to be distinct. But while I only want 1 distinct row to return, I would also like to know how many rows there are without having to do another query, This is what i got so far: </p>
  
  <pre><code>SELECT DISTINCT a.title,
                  a.lat,
                  a.lon,
                  a.alert_content_id,
                  a.date_added,
                  count(*) 
  FROM `reply` r 
  INNER JOIN `alerts` a ON r.alert_id=a.alerts 
  WHERE `user` = '1'
  </code></pre>
  
  <p>This one counts how many rows there are in reply and returns only one row. but it needs to be rows that have this where claus: <code>r.alert_id=a.alerts</code></p>
  
  <p>this returns all the rows but misses the count for each individual row </p>
  
  <pre><code>SELECT DISTINCT a.title,
                  a.lat,
                  a.lon,
                  a.alert_content_id,
                  a.date_added 
  FROM `reply` r 
  INNER JOIN `alerts` a ON r.alert_id=a.alerts 
  WHERE `user` = '1'
  </code></pre>
  
  <p>There was an answer but it didn't quite return what i needed, i need to count all the reply's from the alert the user has commented on, not just count the number of times that user commented on that alert.</p>
  
  <p>Could someone point me in the right direction? Or provide an answer? Thx.</p>