MySQL SUM () grouping by the same ID in different columns

You can use the UNION ALL operator to put the columns one after the other, however horrible it may be.

SELECT ID_PRODUCT_1 as id_product, QUANTITY_PRODUCT_1 as quantity FROM MITABLA;
  UNION ALL
SELECT ID_PRODUCT_2 as id_product, QUANTITY_PRODUCT_2 as quantity FROM MITABLA;
  UNION ALL
SELECT ID_PRODUCT_3 as id_product, QUANTITY_PRODUCT_3 as quantity FROM MITABLA;
  UNION ALL
SELECT ID_PRODUCT_4 as id_product, QUANTITY_PRODUCT_4 as quantity FROM MITABLA;
  UNION ALL
SELECT ID_PRODUCT_5 as id_product, QUANTITY_PRODUCT_5 as quantity FROM MITABLA;

( PD1 : this I would put it in a view, to avoid having to put this ugly query in the whole code)

( PD2 : it is not really necessary that the columns are called the same when making a UNION, it is enough that they are of the same type, in your case, integer.) I wrote the query so that it is understood what am I doing?)

that gives you a result of the form:

id_product  quantity
1001        1400
1002        600
1003        1400 
1001        1000
1002        1400
1003        600
1002        200

(here you could also have the date, which may be useful for future effects)

Then you can operate on that result doing (this will be a long block)

SELECT id_product, SUM(quantity) FROM 
  (
  SELECT ID_PRODUCT_1 as id_product, QUANTITY_PRODUCT_1 as quantity FROM MITABLA;
    UNION ALL
  SELECT ID_PRODUCT_2 as id_product, QUANTITY_PRODUCT_2 as quantity FROM MITABLA;
    UNION ALL
  SELECT ID_PRODUCT_3 as id_product, QUANTITY_PRODUCT_3 as quantity FROM MITABLA;
    UNION ALL
  SELECT ID_PRODUCT_4 as id_product, QUANTITY_PRODUCT_4 as quantity FROM MITABLA;
    UNION ALL
  SELECT ID_PRODUCT_5 as id_product, QUANTITY_PRODUCT_5 as quantity FROM MITABLA;

  ) supertabla
GROUP BY id_product

Now, why UNION ALL and not UNION? That's because UNION only makes you a distinct one, and in your scheme there's nothing wrong with having two sales of the 1001 product for the same amount, and we do not want to leave only the unique combinations.

As he said @mejiapereznestor, this is a very irregular scheme, and it is far from the concept of normalization, to begin with. What happens if someone buys more than 5 products at once? Should we make a second invoice?

Anyway, sometimes you take a legacy project in your hands and you have no choice but to play with those rules, because the business logic is so coupled to the data model that you can not touch any piece at risk of break everything.

Greetings.

I think the logic of your table is incorrect. I show you an example, your table of records would be as follows for this you will need 2 tables: This would be the first table: Table products:

+-----------------+
|id               |
|producto         |
+-----------------+

the second table would be the next one. table records:

+-----------------+
|id               |
|fkidProducto     |//fkidProducto es el id del producto de la tabla anterior
|cantidad         |
|date             |
+-----------------+

Since you have these two tables you do the following. you add the 5 products to your table products. suppose you have these records in the first table

+---------------+
|id   |producto |
+---------------+
|1    |jabon    |
|2    |aceite   |
|3    |pasta    |
|4    |atun     |
|5    |gelatina |
+---------------+

and when you want to add an entry to your record table inserted (id, Fkidproduct, quantity, date)

then your records would be like this:

  *----------------------------------------------------------+
    |ID           |  FkidProduncto |   cantidad | fecha      |
    |    1        |      1         |     10     | 2017-02-03 |
    |    2        |      4         |      5     | 2017-02-03 |
    |    3        |      5         |    20      |2017-02-03  |
    |    4        |      5         |    3       |2017-02-03  |
    +--------------------------------------------------------+

then and so on to add entries in your table records

since you have this you are doing your corresponding select query

select sum(cantidad) where FkidProducto=5 

taking into account the values that I gave you as an example the query should be returned 23

I hope I can solve your problem