sum the column in table not produce correct result

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

This query sum will not produce the correct result. I did subtraction for TotalRevenue and addition for TotalPayment. This result is not correct.

This is my output:

totalsales  totlatax   totalagentsales   filter       TotalRevenue   TotalPayment
38.9开发者_开发百科9       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

---

Not knowing anything about the layout of your tables, the data in your tables, nor what you expect vs. what you have, I'm just guessing here. First, you probably need to repeat the use of SUM for the other instances where you access mc_gross and tax. Also, I think your use of subqueries is messing you up, in particular the two instances of SELECT SUM(totalamount) FROM table1. I imagine you want to limit the rows used in the calculation of these sums similarly to how you do with totalagentsales. You can reuse the totalagentsales value if you move it into the FROM clause as follows:

SELECT SUM(p.mc_gross) AS totalsales, SUM(p.tax) AS totlatax, 
       q.totalagentsales, DATE_FORMAT(p.payment_date, '%d-%m-%Y') AS filter,
       (SUM(p.mc_gross) - SUM(p.tax) - q.totalagentsales) AS TotalRevenue,
       (SUM(p.tax) + q.totalagentsales) AS TotalPayment
FROM table2 AS p,
     (SELECT SUM(totalamount) totalagentsales FROM table1
      WHERE DATE_FORMAT(paydate, '%d-%m-%Y') = DATE_FORMAT(p.payment_date, '%d-%m-%Y')
     ) AS q
GROUP BY filter 
LIMIT 0, 30

Edit

SELECT SUM(p.mc_gross) AS totalsales, SUM(p.tax) AS totlatax, 
       q.totalagentsales, DATE_FORMAT(p.payment_date, '%d-%m-%Y') AS filter,
       (SUM(p.mc_gross) - SUM(p.tax) - q.totalagentsales) AS TotalRevenue,
       (SUM(p.tax) + q.totalagentsales) AS TotalPayment
FROM table2 AS p
     LEFT JOIN (SELECT SUM(totalamount) totalagentsales,
                       DATE_FORMAT(paydate, '%d-%m-%Y') AS filter
                FROM table1 GROUP BY filter) AS q
               ON DATE_FORMAT(p.payment_date, '%d-%m-%Y') = q.filter
GROUP BY filter 
LIMIT 0, 30