title: SQL. How to work/compare/find differences within different rows in the same table
body: <p>I have a table which looks like this:</p>
  
  <pre><code>ID   Date      Size   Marked
  1    2010-02-02    2        X
  2    2002-02-02    1
  1    2010-02-03    2        X
  2    2010-02-03    3        
  3    2010-02-03    4        X
  </code></pre>
  
  <p>And I have a code (PHP) which does following things:
  a) Calculate sum of sizes per day
  b) Find the difference between total for this day and last day.
  c) Find the sum of sizes for rows which became marked this day (the row with the same id wasn't marked yesterday).</p>
  
  <p>As example, I will get following results:</p>
  
  <pre><code>Date        Total  DiffWithYesterday  MarkedThisDay
  2010-02-02    3      0                  0
  2010-02-03    9      6                  4
  </code></pre>
  
  <p>I have feeling that there is a way to write this in SQL. However I am quite weak in SQL, so I gave up after a day playing around inner joins, group by and embedded selects.</p>
  
  <p>I would appreciate, if you give me some clues how to do that.</p>
  
  <p>Oh.. And I am using MySQL. </p>
  
  <p>Regards,
  Victor</p>