How to substract on sum() field from rows "grouped by"

Question

Having the following table

+------------------------+
| id | product_id | units|
+------------------------+
|  1 | 6949       |   3  |
|  2 | 6949       |   7  |
|  3 | 6912       |   0  |
|  . |   .        |   .  |
+------------------------+

I want to count how many units are there linked to the product id, so I use the following query:

SELECT id, product_id, SUM(units) AS units FROM table GROUP BY product_id;

Which will return

+------------------------+
| id | product_id | units|
+------------------------+
|  1 | 6949       |   10 |
|  2 | 6912       |   0  |
+------------------------+

How could I use "update" to substract 1 unit from any product using its product_id? Thanks

Answer 1

Maybe I forgot to menction what I care about is the sum() result, not if the units field of every row is correct. I was afraid a simple update would affect all rows with that product_id so the query would be

UPDATE table SET units = units - 1 WHERE product_id = 6949 LIMIT 1;

Answer 2

The output of your query doesn't reflect the contents of your table, the id field for product 6912 can not be 2 unless it's a rownum as @Tim suggests. I'm guessing it's a typo, so to explicitly uses the id in the results table lets explicitly choose the one we want;

select min(id) min_id, product_id, sum(units) agg_units, sum(units) - 1 agg_units_minus_one from product group by product_id order by min_id;

+------+------------+-----------+---------------------+
| id   | product_id | agg_units | agg_units_minus_one |
+------+------------+-----------+---------------------+
|    1 |       6949 |        10 |                   9 |
|    3 |       6912 |         0 |                  -1 |
+------+------------+-----------+---------------------+

Now if you really want to update the units we at least have an unique id/product combo to use and a value to assign, but I suspect this is not what you want (a future aggregations will be wrong).

Answer 3

Try

UPDATE table SET units = units - 1 WHERE product_id = ?

Answer 4

Your question is just asking for a basic GROUP BY query, and here a valid one:

SELECT
    product_id,
    SUM(units) AS units
FROM yourTable
GROUP BY
    product_id;

But your question has a twist, because the id appearing in the sample result set actually appears to be a row number, ordered by one of the id values in each product group. We can try writing this query:

SELECT
    ROW_NUMBER() OVER (ORDER BY MIN(id)) id,
    product_id,
    SUM(units) AS units
FROM yourTable
GROUP BY
    product_id;

Demo