I have a table which has below structure. I'm using phpmyadmin 3.4.5, mysql version 5.5.16.
Table
Invoice_ID - PO_ID- Product - Quantity Invoice_Qty - Amount
Inv1 - PO1 - P1 - 100 - 50 - 1000
Inv2 - PO1 - P1 - 100 - 50 - 1000
Inv3 - PO2 - P2 - 50 - 20 - 500
Inv4 - PO2 - P2 - 50 - 20 - 500
Inv5 - PO2 - P3 - 50 - 10 - 250
What I'm really want to do is that
If Previous Row of PO_ID and and Product Name is Same as current Row PO_ID and Product then Quantity of current row should be zero?
Sum of Invoice_Quantity = Quantity. So Required like below
My Expected Output given below:
Out Put:
Invoice_ID - PO_ID- Product - Quantity Invoice_Qty - Amount
Inv1 - PO1 - P1 - 100 - 50 - 1000
Inv2 - PO1 - P1 - 0 - 50 - 1000
Inv3 - PO2 - P2 - 50 - 20 - 500
Inv4 - PO2 - P2 - 0 - 20 - 500
Inv5 - PO2 - P3 - 0 - 10 - 250
I tried the How to get result set like Oracle lag function. But It not worked for me.
And tried to write a procedure for that. I'm stuck with export resutlset. That is I don't know how to assign and get the result set.
Please help me out this problem.
Your sqlfiddle was confusing. Please don't provide sample data here and then use different sample data in the sqlfiddle. And your desired result here is wrong, since you said in the description
If Previous Row of PO_ID and and Product Name is Same as current Row PO_ID and Product then Quantity of current row should be zero
Anyway, used my own...
select
t.*,
if(@previd = po_id and @prevprod = Product, 0, Quantity) AS new_quantity,
@previd := po_id,
@prevprod := product
from
t
, (select @previd:=null, @prevprod:=null) var_init
order by po_id, product
Note, that the order in the select clause is important, as well as the order by clause.