mysql - Optimizing Sql Query For Better Performance -

mysql - Optimizing Sql Query For Better Performance -

is possible design next query works more efficiently? have inventory table lists purchased , sold products. aim calculate net remaining quantity of products in our inventory.

select stokkodu, girenmiktar, sum(girenmiktar)-sum(if(irsaliyelicikanmiktar null, 0, irsaliyelicikanmiktar)) gorunurkalan,sum(girenmiktar)-(sum(if(irsaliyelicikanmiktar null, 0, irsaliyelicikanmiktar))+sum(if(irsaliyesizcikanmiktar null, 0, irsaliyesizcikanmiktar))) gercekkalan inventory stokkodu in(select stokkodu inventory girenmiktar>0) grouping stokkodu

when purchase new product. enters table new stokkodu (stock id) , quantity girenmiktar. whenever begin sell, there 2 kinds of procedure subtract girenmiktar. selling invoice (irsaliyelicikanmiktar), selling waybill (irsaliyesizcikanmiktar).

for example:

id     stokkodu     girenmiktar     irsaliyelicikanmiktar     irsaliyesizcikanmiktar 1      sku1           100 2      sku1                                  203      sku1                                                                       104      sku2           300 5      sku2                                  506      sku2                                                                       30

result must be:

stokkodu    girenmiktar    gorunurkalan    gercekkalan sku1          100                80(100-20)        70(100-20-10)sku2          300                250(300-50)      220(300-50-30)

of course of study there few more description columns such productname, date etc. in result set returns rows girenmiktaris greater zero.

stokkodu means identification describes product. girenmiktar means quantity of purchased product. irsaliyelicikanmiktar means quantity of sold product issued invoice. irsaliyesizcikanmiktar means quantity of sold product has not issued invoice yet.

many helps.

your where / in part not neccessary. seek this:

select stokkodu, girenmiktar, sum(coalesce(girenmiktar,0)) -sum(coalesce(irsaliyelicikanmiktar, 0)) gorunurkalan, sum(coalesce(girenmiktar,0)) - sum(coalesce(irsaliyelicikanmiktar, 0)) + sum(coalesce(irsaliyesizcikanmiktar, 0)) gercekkalan, birim, fiyat, tedarikciid inventory grouping stokkodu order tarih desc;

mysql optimization inventory