How to insert SUM() function that sums rows with similar ID in a code part of witch is unchangeable?

I am trying to write a quarry in a module for Dolibarr ERP. But module hase a part of code that is predefined and can not be changed. And I need to insert a SUM() function in it that will combine rows with similar id. That i know how to do in a regular MySQL:

SELECT fk_product AS prod, SUM(value) AS qty
FROM llx_stock_mouvement
WHERE type_mouvement = 2 AND label LIKE 'SH%'
GROUP BY fk_product
ORDER BY 1 DESC
LIMIT 26

that gives me what I want :

prod    qty 
 1      13 
 2      10

BUT module has a predefined unchangeable code :

this part is predefined module writes it himself based on values provider in it:

SELECT DISTINCT       
 c.fk_product AS com,  
 c.value AS qty

THIS PART I CAN WRITE IN A MODULES GUI:

FROM                        
    llx_stock_mouvement AS c 
 WHERE                        
   type_mouvement = 2        
 AND label LIKE 'SH%'

And this part is predefined:

ORDER BY 1 DESC         
LIMIT 26

I would appreciate any help and advice on question is there any workaround that can be done to make my desired and result ampere ? As it would using the first code I posted ?

Answer

If you can only modify the bit in the middle box then you might need to use a subquery;

--fixed part
SELECT DISTINCT      
c.fk_product AS com, 
c.value AS qty 

--begin your editable part
FROM
(
  SELECT fk_product,
  SUM(value) AS value
  FROM llx_stock_mouvement
  WHERE type_mouvement = 2 AND label LIKE 'SH%'
  GROUP BY fk_product
) c
--end your editable part

--fixed part
ORDER BY    1
DESC
LIMIT 26

Advertisement

Answer