Databases
StackOverflow2013
Postsdbo
POresult sum does not match
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POresult sum does not match
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSunarto
    1. USSunarto
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload