Search code examples
mysqlperformancereturnsubtraction

MySQL query to subtract two groups of values

I have a table for "invoice details", I want to get net_sales for the item#, which could be achieved by subtracting sales from return.

The question is: can I make a query that do this calculation, by getting SUM(qty) of type = 'S' and SUM(qty) of type = 'R' then subtracting them to get net_sales for the item#? also if there's more than one item# to get net_sales for?

+------+------+------+
| Item |  Qty | Type |
+------+------+------+
| 1132 |  1   |  S   |
| 1132 |  2   |  S   |
| 1132 |  3   |  R   |
+------+------+------+

What I did is selecting first group 'S' in query, then select second group 'R' in another query, Then subtract them, which leads to lack of performance if there's many items# to deal with!

Solution

  • This becomes easy, if you phrase the problem differently: Instead of SUM()ing all S, then subtracting the sum of all R you can just as well calculate the sum of all items, with R being a marker for negative:

    SELECT
  `Item`,
  SUM(IF(`Type`='S', `Qty`, -1*`Qty`)) AS total
FROM
  `invoice_details`
WHERE
  -- your criteria here, e.g.
  `Item`=1132
GROUP BY `Item`