title: sum the column in table not produce correct result
body: <pre><code>SELECT
  SUM(p.mc_gross) AS totalsales,
  SUM(p.tax) AS totlatax, 
  (SELECT SUM(totalamount) FROM table1 
      WHERE DATE_FORMAT(paydate, '%d-%m-%Y') = 
      DATE_FORMAT(p.payment_date, '%d-%m-%Y')) AS totalagentsales, 
  DATE_FORMAT(p.payment_date, '%d-%m-%Y') AS filter,
  (p.mc_gross - p.tax - (SELECT SUM(totalamount) FROM table1)) AS TotalRevenue,
  (p.tax + (SELECT SUM(totalamount) FROM table1) ) AS TotalPayment
  FROM table2 AS p GROUP BY filter LIMIT 0, 30
  </code></pre>
  
  <p>This query sum will not produce the correct result. I did subtraction for <code>TotalRevenue</code> and addition for <code>TotalPayment</code>. This result is not correct.</p>
  
  <p>This is my output:</p>
  
  <pre><code>totalsales  totlatax   totalagentsales   filter       TotalRevenue   TotalPayment
  38.99       3.54       NULL              11-03-2011   33.152         4.54
  6           0.09       NULL              14-02-2011   3.612          1.09
  2177.46     197.96     0.899             14-03-2011   1977.202       198.96
  299.94      27.27      1.399             15-03-2011   270.372        28.27
  19.98       1.82       NULL              21-01-2011   15.862         2.82
  3           0.27       NULL              22-01-2011   0.432          1.27
  14.77       1.82       NULL              28-02-2011   10.652         2.82
  </code></pre>