Multiplying values of same PKs

Question

I have the following table:

+-----+------+
| qwe | asdd |
+-----+------+
| a   | 3    |
| a   | 4    |
| b   | 5    |
| b   | 6    |
+-----+------+

The result should be something like this:

+-----+------+
| qwe | asdd |
+-----+------+
| a   | 12   |
| b   | 30   |
+-----+------+

I wrote a code that may be only applied to the actual table, but if we add a row or more, it is not working well:

select qwe, (SUM(asd) - MIN(asd)) * MIN(asd) a from t
group by qwe

How would you recommend me to modify this code to make it work properly with tables like this?

+-----+------+
| qwe | asdd |
+-----+------+
| a   | 3    |
| b   | 4    |
| b   | 5    |
| a   | 6    |
| a   | 7    |
+-----+------+

And get table like this:

+-----+------+
| qwe | asdd |
+-----+------+
| a   | 12   |
| b   | 126  |
+-----+------+

Answer 1

There is no built in PRODUCT() function. Alas.

Assuming all your values are positive, you can do:

select qwe, exp(sum(log(asdd))) as aggregate_product
from t
group by qwe;

Note: This can be extended to handle 0 and negative values. That just adds a lot of extra stuff to the expression, which hides the fundamental logic.

To prevent problems with zero:

select qwe, coalesce(exp(sum(log(nullif(asdd, 0)))), 0) as aggregate_product

Negative numbers are a bit trickier.