title: result sum does not match
body: <p>I have a table schema like this.</p>
  
  <pre><code>mysql&gt; desc material_out;
  
  +-------------+--------------+------+-----+---------+-------+
  | Field       | Type         | Null | Key | Default | Extra |
  +-------------+--------------+------+-----+---------+-------+
  | id          | int(11)      | NO   |     | 0       |       |
  | barcode     | varchar(20)  | NO   |     | NULL    |       |
  | name        | varchar(100) | NO   |     | NULL    |       |
  | qty         | double       | YES  |     | NULL    |       |
  | unit        | varchar(20)  | YES  |     | NULL    |       |
  | num_letter  | varchar(30)  | YES  |     | NULL    |       |
  | date        | date         | YES  |     | NULL    |       |
  | destination | varchar(50)  | YES  |     | NULL    |       |
  +-------------+--------------+------+-----+---------+-------+
  8 rows in set (0.00 sec)
  
  mysql&gt; desc material_in;
  
  +-------------+--------------+------+-----+---------+-------+
  | Field       | Type         | Null | Key | Default | Extra |
  +-------------+--------------+------+-----+---------+-------+
  | id          | int(11)      | NO   |     | 0       |       |
  | barcode     | varchar(20)  | NO   |     | NULL    |       |
  | name        | varchar(100) | NO   |     | NULL    |       |
  | qty         | double       | YES  |     | NULL    |       |
  | unit        | varchar(20)  | YES  |     | NULL    |       |
  | num_letter  | varchar(30)  | YES  |     | NULL    |       |
  | date        | date         | YES  |     | NULL    |       |
  | destination | varchar(50)  | YES  |     | NULL    |       |
  +-------------+--------------+------+-----+---------+-------+
  8 rows in set (0.00 sec)
  
  mysql&gt; desc goods;
  
  +-------------+--------------+------+-----+---------+-------+
  | Field       | Type         | Null | Key | Default | Extra |
  +-------------+--------------+------+-----+---------+-------+
  | id          | int(11)      | NO   |     | 0       |       |
  | barcode     | varchar(20)  | NO   |     | NULL    |       |
  | name        | varchar(100) | NO   |     | NULL    |       |
  | unit        | varchar(20)  | YES  |     | NULL    |       |
  | category    | varchar(25)  | YES  |     | NULL    |       |
  | first_stok  | double    )  | YES  |     | NULL    |       |
  +-------------+--------------+------+-----+---------+-------+
  6 rows in set (0.00 sec)
  </code></pre>
  
  <p>In table material_out I have data of 10,000 rows. and has as many as 350 types of barcodes. In table material_in I have data around 15,000 rows. and has as many as 200 types of barcodes.</p>
  
  <p><strong>my query is like this</strong></p>
  
  <pre><code>SELECT br.barcode,
      COALESCE(tNEW.total_out,0) AS total_out, COALESCE(tNEW.total_in,0) as total_in,
      COALESCE(tNEW.total_in,0)-COALESCE(tNEW.total_out,0) AS result,
      COALESCE(tOLD.total_out,0) AS total_out_old, COALESCE(tOLD.total_in,0) AS total_in_old
  FROM (
  SELECT barcode from goods where category=1
  ) as br
  LEFT JOIN (
      SELECT goods.barcode,
          COALESCE(SUM(tOUT.qty),0) AS total_out,
          COALESCE(SUM(tIN.qty),0) AS total_in
      FROM goods
      LEFT JOIN material_out AS tOUT ON tOUT.barcode=goods.barcode
      LEFT JOIN material_in AS tIN ON tIN.barcode=goods.barcode
      WHERE goods.category=1
          AND tOUT.date &gt;='2013-05-01' AND tOUT.date &lt;='2013-08-31'
          AND tIN.date &gt;= '2013-05-01' AND tIN.date &lt;= '2013-08-31'
      GROUP BY goods.barcode
  ) AS tNEW ON tNEW.barcode=br.barcode
  LEFT JOIN (
      SELECT goods.barcode,
          SUM(tOUT.qty) AS total_out,
          SUM(tIN.qty) AS total_in
      FROM goods
      LEFT JOIN material_out AS tOUT ON tOUT.barcode=goods.barcode
      LEFT JOIN material_in AS tIN ON tIN.kode=goods.barcode
      WHERE goods.category=1
          AND tOUT.date BETWEEN '2013-01-01' AND '2013-04-31'
          AND tIN.date BETWEEN '2013-01-01' AND '2013-04-31'
      GROUP BY goods.barcode
  ) AS tOLD ON tOLD.barcode=br.barcode
  </code></pre>
  
  <p>I have used a query like that. the result is going well. but when I try to enter new data. I then re-query of your display. but the results are not in accordance with the entered data. for example, when I enter a query from you. The following results</p>
  
  <pre><code>+--------------+-----------+----------+------------+---------------+-------------------+
  |    barcode   | total_out | total_in | result     | total_out_old | total_in_old      |
  +--------------+-----------+----------+------------+---------------+-------------------+
  | TNWET021     |      6195 |    15000 |       8805 |         20085 |             46200 |
  | TNWET020     |      3420 |     7650 |       4230 |          4860 |             23925 |
  | TNWET019     |      8370 |    25200 |      16830 |         11610 |             47175 |
  | TNWET018     |     18690 |    44100 |      25410 |         13800 |             54150 |
  | TNWET017     |      1140 |     3750 |       2610 |          3690 |             16200 |
  | TNWET016     |     19500 |    56100 |      36600 |         31725 |            111300 |
  | TNWET015     |      5145 |    18150 |      13005 |          6510 |             23400 |
  | TNWET014     |     33300 |    65250 |      31950 |         96300 |            262500 |
  | TNWET013     |      1170 |     5625 |       4455 |          3690 |             13200 |
  | TNWET012     |       720 |     2700 |       1980 |          3870 |             13800 |
  | TNWET011     |         0 |        0 |          0 |           180 |               450 |
  | TNWET010     |         0 |        0 |          0 |           405 |              1125 |
  | TNWET009     |         0 |        0 |          0 |             0 |                 0 |
  </code></pre>
  
  <p>When I do a manual summation and results like this.</p>
  
  <pre><code>mysql&gt; select sum(qty) from material_in where barcode='TNWET021' and date BETWEEN '2013-05-01' AND '2013-08-31';
  +-------------+
  |   sum(qty)  |
  +-------------+
  |         750 |
  +-------------+
  1 row in set (0.00 sec)
  
  mysql&gt; select sum(qty) from material_in where barcode='TNWET020' and date BETWEEN '2013-05-01' AND '2013-08-31';
  +-------------+
  |   sum(qty)  |
  +-------------+
  |         450 |
  +-------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Why the results can be very much different, when the data is very vital for reporting. Please help me.</p>
  
  <p>I used this earlier but it took a long time to execute. maybe you can help to summarize this query to quickly executed</p>
  
  <pre><code>SELECT COALESCE(tIN.total_in,0) + COALESCE(production.total_prod,0) AS incoming, COALESCE(tOUT.total_out,0) AS expenditure,br.barcode as barcode, br.name,br.initial_stock,br.unit,COALESCE(adj.total,0) AS adjusment,COALESCE(tIN.total_in,0) + COALESCE(production.total_prod,0) + COALESCE(adj.total,0) + COALESCE(br.intial_stock,0) - COALESCE(tOUT.total_out,0) as final_stok,so.stock_opname from (
  select barcode,name,initial_stock,unit from barang where category=1
  ) as br
  LEFT JOIN (
  select (select sum(qty) from material_out where date &gt;= '2013-05-01' AND date &lt;='2013-08-31' and barcode=a.barcode) as total_out,a.barcode from material_out a group by a.barcode
  ) as tOUT
  ON tOUT.barcode=br.barcode
  LEFT JOIN
  (
  SELECT(
  SELECT SUM(qty) FROM adjusment
  WHERE status = '+' AND date &gt;= '2013-05-01' AND date &lt;= '2013-08-31'
  ) - (
  SELECT SUM(qty) FROM adjusment
  WHERE status = '-' AND date &gt;= '2013-05-01' AND date &lt;= '2013-08-31'
  ) AS total,barcode FROM adjusment
  GROUP BY barcode
  ) AS adj
  ON br.barcode = adj.barcode
  LEFT JOIN (
  select (select sum(qty) from material_in where date &gt;= '2013-05-01' AND date &lt;='2013-08-31' and barcode=a.barcode) as total_in,a.barcode,a.nama from material_in a group by a.barcode
  ) as tIN
  ON br.barcode=tIN.barcode
  LEFT JOIN (
  select (select sum(qty) from view_production where date &gt;= '$start' AND date &lt;='$end' and kode=a.kode) as total_prod,a.barcode from view_production a group by a.barcode
  ) as production
  ON br.barcode=production.barcode
  
  LEFT JOIN (
  select(select sum(qty) from stock_opname where date &gt;= '2013-04-01' AND date &lt;= '2013-05-31' AND barcode=a.barcode) as stok_opname,a.barcode from pencacahan a group by a.barcode
  ) as so
  ON br.barcode=so.barcode
  </code></pre>