title: MySQL compare two values on same table
body: <p>I'm trying to compare two values in the same table, and check if there is a difference.
  Right now, I have 1485 records in the cms_statistics_pages table, but when the query below:</p>
  
  <pre><code>SELECT
      cp.identifier,
      COUNT(csp1.statID) AS hits,
      COUNT(csp2.statID) AS hits_yesterday,
      IF(COUNT(csp1.statID)&gt;COUNT(csp2.statID),1,0) AS growth
  FROM cms_pages cp
  LEFT JOIN cms_statistics_pages csp1
        ON csp1.pageID = cp.pageID
        AND DATE(csp1.datetime) = '2012-07-20'
  LEFT JOIN cms_statistics_pages csp2
        ON csp2.pageID = cp.pageID
        AND DATE(csp2.datetime) = '2012-07-19'
  GROUP BY cp.identifier
  </code></pre>
  
  <p>..is fired, I get these results:</p>
  
  <pre><code>identifier                  hits    hits_yesterday  growth
  index                       13395   13395           0
  siden-er-under-opdatering   638     638             0
  vores-historie              0       3               0
  </code></pre>
  
  <p>Which is not correct for my purpose. Then if I change:</p>
  
  <pre><code>AND DATE(csp1.datetime) = '2012-07-20'
  </code></pre>
  
  <p>to a date that will match no records</p>
  
  <pre><code>AND DATE(csp1.datetime) = '2012-07-21'
  </code></pre>
  
  <p>My result now looks like this:</p>
  
  <pre><code>identifier                  hits   hits_yesterday   growth
  index                       0      141              0
  siden-er-under-opdatering   0      29               0
  vores-historie              0      3                0
  </code></pre>
  
  <p>Now the hits are correct, so I'm wondering if the query counts the records multiple times when both the joins contains some data.</p>
  
  <p>Example data from cms_pages:</p>
  
  <pre>
  
      pageID  sectionID   templateID  identifier  default title exclude_title 
      1       1       1           index   1       Welcome to SiteTech Framework 2012
  
  </pre>
  
  <p>Example data from cms_statistics_pages:</p>
  
  <pre>
  
      statID  frontend    backend     pageID  sectionID   panel   datetime
      1       0       1       34      6       admin   2012-07-17 12:34:14
  
  </pre>