How to remove duplicates and sum cells?

Question

SQL

Hello. There is such a table:

+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+
| id | date_id                 | year | week | store_id | store_id_new | good_id | promo | quantity | order  | cost |
+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+
| 1  | 2015-12-07 00:00:00.000 | NULL | NULL | 5        | NULL         | 26      | 0     | 2        | 764536 | NULL |
+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+
| 2  | 2015-12-07 00:00:00.000 | NULL | NULL | 5        | NULL         | 26      | 0     | 1        | 764536 | NULL |
+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+

As you can see, four fields (date_id, store_id, good_id and order) have the same value. I would like to update the table, while summing the quantity field of the rows that have the same value for the 4 above fields, i.e. get something like this:

+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+
| id | date_id                 | year | week | store_id | store_id_new | good_id | promo | quantity | order  | cost |
+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+
| 1  | 2015-12-07 00:00:00.000 | NULL | NULL | 5        | NULL         | 26      | 0     | 3        | 764536 | NULL |
+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+

Answer 1

So like
select id , date_id, year , week , store_id , store_id_new , good_id , promo , SUM(quantity) , order , cost
from table
group by
id , date_id, year , week , store_id , store_id_new , good_id , promo , order , cost
That is, in the GROUP BY field we add all the fields for which arithmetic operations are not needed. In this case, I saw only quantity

Answer 2

If this is really necessary, then the easiest way is to insert a selection into a temporary table within the transaction, delete "duplicates" from the main one and insert the aggregation result from the temporary into the main one. Here the question really arises id (I hope it is pk?)

Answer 3

Criteria for removing duplicates: leave with a minimum id?
You make a cursor with grouping by date_id, store_id, good_id и orderand a filter: the number of lines is more than one. For each row of the cursor, determine the minimum idand update quantitythe sum over date_id, store_id, good_id и order. Delete the extra ones. Move to the next line of the cursor.