CODEWITHSUNDEEP
phpmysqlsqldatabase
Zainul Abideen
Zainul Abideen

Reputation: 1900

display sales through sql query after subtracting sales return of same product and bill number

I have a table(sales) in my database which records sales and sale return. enter image description here

sale returns are recorded with the status='sr' I want to display final quantity of each product after deducting the sale returns. example for the product B0801180323NA there is a sales return with quantity:2 and also sale with quantity:2 so the final quantity which has to be displayed now is 0.

Upvotes: 1

Views: 990

Answers (4)

Wince
Wince

Reputation: 21

select sum(a.quantity) - coalesce(sum(b.quantity), 0) as total_quantity, a.bill_id, a.product_id 
from sales a
left join sales b on a.bill_id = b.bill_id and a.product_id = b.product_id
and b.status = 'sr'
where a.status = 'sales'
group by a.bill_id, b.product_id

Upvotes: 1

Tum
Tum

Reputation: 7585

SELECT products, SUM(IF(status='sale',quantity,0)) - SUM(IF(status='sr',quantity,0)) AS quantity 
FROM sales
GROUP BY bill_id, products;

This counts the quantities based on the 'status' column, subtracting the 'sale' ones from the 'sr' ones.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a join with same table and differnce on the sum group by products

select a.products, sum(a.quantity) -  ifnull(sum(b.quantity),0)
from sales 
left join sales on a.products = b.products and b.status = 'sr'
where a.status = 'sale' 
group by a.products

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I would use subqueries

select products,
       (
          select sum(quantity) from sales s2 where s2.products = s1.products and status = 'sale'
       ) -
       (
          select sum(quantity) from sales s2 where s2.products = s1.products and status = 'sr'
       )
from (select distinct products from sales) s1

Upvotes: 1

Related Questions