0

I need to get all values operated with a MULTIPLY() instead of a SUM() in a query like this (simplified):

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

I'm looking for a light-weight solution, without creating a view or a temporary table, I'm just looking for a function like MULTIPLY() in OpenOffice.

My objective is to have negative, positive and zero cells when exists some products with prop=0, some negative prop products, or all positive prop products, it is for visual purpose. Any idea? I see MySQL don't have a MULTIPLY() function :/

Windgate
  • 365
  • 1
  • 4
  • 14
  • Imagine you have two records with different prices, e.g. $1000 and $2000. `SUM(al.price)` would return $1000 + $2000 = $3000. What is `MULTIPLY(al.price)` supposed to return? $1000 * $2000 = $2,000,000? That's silly. – Ruud Helderman Feb 24 '20 at 11:42
  • Does this answer your question? [SQL Server Query - groupwise multiplication](https://stackoverflow.com/questions/3653586/sql-server-query-groupwise-multiplication) – nbk Feb 24 '20 at 11:43
  • Does this answer your question? [Multiplication aggregate operator in SQL](https://stackoverflow.com/questions/5416169/multiplication-aggregate-operator-in-sql) – mitkosoft Feb 24 '20 at 11:44
  • Let's say... I have simplified the query, I don't want to multiply prices, I want to multiply properties with values between -1 and +5, and I need to check if exists any negative (and no zero), any zero, or all positive properties, I will edit the code... – Windgate Feb 24 '20 at 11:45

1 Answers1

1

I need to check if exists any negative (and no zero), any zero, or all positive properties

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
Akina
  • 39,301
  • 5
  • 14
  • 25
  • This is ok and solve my problem, thanks! But I keep on mind MySQL should have a MULTIPLY() and other functions that OpenOffice has for "Excel" files :P – Windgate Feb 24 '20 at 12:04
  • @Windgate DBMS server (like MySQL) is not a table processor (like Excel). Their built-in functions lists will always differ, because some function implemented in the tool from one group may easily make no sense for another group. Like MULTIPLY() function... – Akina Feb 24 '20 at 12:31