CODEWITHSUNDEEP
mysql
Nick
Nick

Reputation: 1869

MySql query : get items with sales >= a certain percentage

From the following table how can i get items which have sales>= 50% for the last few days ?

date        item_id  in_stock  out-stock
2011-06-20    352       50        30
2011-06-21    351       10        1
2011-06-22    332       23        20
2011-06-23    311       12         7

Currently i'm using a query which calculates the percentage of sales and using php i'm looping through every item and gets items with or above 50% of sales.

SELECT i.id as item_id,i.item,SUM(dpr.out_stock)/SUM(dpr.in_stock) * 100 as p,SUM(dpr.out_stock) as sales,u.title as unit,u.id as unit_id   
     FROM `sm_daily_item_stock_report` as dpr
  INNER JOIN sm_item_master as i on dpr.item_id=i.id and i.consumable='1'
  INNER JOIN sm_unit_master as u on i.primary_unit=u.id
  WHERE date between '2011-06-20' and '2011-06-23'
  and dpr.store_id='1' GROUP BY item_id

But this query returns all products against store_id 1.

Krishnik

Upvotes: 0

Views: 177

Answers (2)

Gajendra Bang
Gajendra Bang

Reputation: 3583

SELECT i.id as item_id,i.item,SUM(dpr.out_stock)/SUM(dpr.in_stock) * 100 as p,SUM(dpr.out_stock) as sales,u.title as unit,u.id as unit_id   
FROM `sm_daily_item_stock_report` as dpr
INNER JOIN sm_item_master as i on dpr.item_id=i.id and i.consumable='1'
INNER JOIN sm_unit_master as u on i.primary_unit=u.id
WHERE date between '2011-06-20' and '2011-06-23'
and dpr.store_id='1' GROUP BY item_id
having (SUM(dpr.out_stock)/SUM(dpr.in_stock) * 100) > 50

Upvotes: 0

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26719

Add HAVING p > 50 to the query :)

Upvotes: 2

Related Questions