如何(MySQL)将group by上的单元格值相乘？

Question

在这样的查询(简化)中，我需要使用MULTIPLY()而不是SUM()来获得所有的值：

SELECT aav.id, COUNT(al.id_product) AS num_products, MULTIPLY(al.prop) AS mul_prop
FROM a_table a 
JOIN v_table aav ON a.id = aav.id_article AND a.fecha > '2020-01-13 11:28:54'
JOIN l_table al ON al.id_article = aav.id_article
JOIN l_table al2 ON al.id_article= al2.id_article AND al2.id_product = 113288
WHERE aav.id_attrib IN (SELECT a.id FROM attrib a WHERE a.key= 'my_key') 
GROUP BY id_article HAVING num_products > 2

我正在寻找一个轻量级的解决方案，而不是创建视图或临时表，我只是想在OpenOffice中寻找一个像MULTIPLY()这样的函数。

我的目标是当存在一些带有prop=0的产品，一些负的道具产品，或者所有的正道具产品时，有负的，正的和零的细胞，这是为了视觉目的。有什么想法吗？我发现MySQL没有MULTIPLY()函数:/

Answer 1

我需要检查是否存在任何负值(并且没有零)、任何零或所有正值属性

SELECT aav.id, 
       COUNT(al.id_product) AS num_products, 
       SUM(al.prop < 0) as count_negative, 
       SUM(al.prop = 0) as count_zeros,
       SUM(al.prop > 0) as count_positive
       -- the rest are NULL
FROM a_table a 
JOIN v_table aav ON a.id = aav.id_article AND a.fecha > '2020-01-13 11:28:54'
JOIN l_table al ON al.id_article = aav.id_article
JOIN l_table al2 ON al.id_article= al2.id_article AND al2.id_product = 113288
WHERE aav.id_attrib IN (SELECT a.id 
                        FROM attrib a 
                        WHERE a.key= 'my_key') 
GROUP BY id_article 
HAVING num_products > 2