CODEWITHSUNDEEP
mysqlsqldatabase
John Nuñez
John Nuñez

Reputation: 1830

MySQL Query Subtraction values

I have this query:

SELECT (
        SELECT purchase_code 
        FROM qa_suppliers_invoices a 
        WHERE a.supplier_invoice_code = b.item_invoicecodesupplier
       ) AS purchase_code, 
      item_code, 
      status_code, 
      itempurchase_quantity, 
      item_costprice 
FROM qa_items_purchases b
ORDER BY purchase_code LIMIT 0,20000;

It return the following data enter image description here

I want to GROUP like a Purple color line using (item_code) row, i want to add all the (itempurchase_quantity) making reference to (status_code) row then apply this ecuation (status_15 - status_16) also like the image.

I want this result, GROUP():

purchase_code  item_code  status_code  itempurchase_quantity  item_costprice
1              1506       15           713                    126.94
2              1503       16           12                     0.00
2              1683       15           9                      25.28
2              1931       15           60                     0.00

In the case of item_code(1997) not exist status_code = 15 There is thus return a negative number(-9) then you do not need to show.

2              2325       16           50                     849.97

NOTE: I need to disappear all 16 status_code

Demo data to play with query at sqlfiddle

UPDATE: This is a picture detailing what I need: enter image description here

Upvotes: 0

Views: 1284

Answers (2)

Gonzalo.-
Gonzalo.-

Reputation: 12672

SELECT 
      a.purchase_code,
      b.item_code, 
      '15' AS status_code,
      SUM(
        CASE b.status_code 
            WHEN 15 THEN b.itempurchase_quantity
        ELSE 
            - (b.itempurchase_quantity) 
        END) 
        AS itempurchase_quantity,
      b.item_costprice
FROM qa_items_purchases b
    INNER JOIN qa_suppliers_invoices a 
        ON a.supplier_invoice_code = b.item_invoicecodesupplier
GROUP BY purchase_code, b.item_code, b.item_costprice
    HAVING itempurchase_quantity > 0
    ORDER BY purchase_code 
    LIMIT 0,20000

Upvotes: 2

Fahim Parkar
Fahim Parkar

Reputation: 31627

Use this

SELECT purchase_code, item_code, status_code, 
     SUM(case status_code 
         when 15 then itempurchase_quantity
         ELSE (-1*itempurchase_quantity) END
        ) AS itempurchase_quantity
FROM myTable
GROUP BY purchase_code;

Demo

NOTE

Instead of myTable, you will need to use your data. I have create myTable with data that you have shown in image.

I think new query would be 

SELECT (
    SELECT purchase_code 
    FROM qa_suppliers_invoices a 
    WHERE a.supplier_invoice_code = b.item_invoicecodesupplier
   ) AS purchase_code, 
  item_code, 
  status_code, 
  SUM(case status_code 
         when 15 then itempurchase_quantity
         ELSE (-1*itempurchase_quantity) END
        ) AS itempurchase_quantity, 
  item_costprice 
FROM qa_items_purchases b
GROUP BY purchase_code
ORDER BY purchase_code 
LIMIT 0,20000;

Update 1

SELECT purchase_code, item_code, status_code, 
       SUM(case status_code 
           when 15 then itempurchase_quantity
           ELSE (-1*itempurchase_quantity) END
          ) AS itempurchase_quantity
FROM myTable
GROUP BY purchase_code, item_code;

New Demo

Upvotes: 2

Related Questions