Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need an aggregate function that MySQL doesn't provide.

I would like it to be in MySQL's flavor of SQL (that is, not in C).

How do I do this? What I'm stuck on is creating an aggregate function -- the docs don't seem to mention how this is done.

Examples of desired usage of a product function:

mysql> select product(col) as a from `table`;
+------+
| a    |
+------+
|  144 |
+------+
1 row in set (0.00 sec)

mysql> select col, product(col) as a from `table` group by col;
+-----+------+
| col | a    |
+-----+------+
|   6 |   36 |
|   4 |    4 |
+-----+------+
2 rows in set (0.01 sec)
share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

According to the documentation http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html it's only possible to write aggregate functions in C. Sorry!

share|improve this answer
    
Either C or C++. Not SQL, anyway. –  Mike Sherrill 'Cat Recall' Oct 18 '12 at 0:49
    
I presume any language which can generate binary libraries in the platform-supported binary format with C calling conventions. –  Colin 't Hart Oct 18 '12 at 7:30
    
I don't know. It was documented as "C or C++ (or another language that can use C calling conventions)" in version 5.0. The docs dropped "or another language that can use C calling conventions" in version 5.1. That's a strange phrase to drop. –  Mike Sherrill 'Cat Recall' Oct 18 '12 at 10:18
add comment

I don't know if there is way to define a new aggregate function, not without messing with MySQL source code.

But if your numbers are all positive, you may well derive from the arithmetic identity:

log( product( Ai ) ) = sum( log( Ai ) )

that you can use EXP(SUM(LOG(x))) to calculate PRODUCT(x). Test in SQL-Fiddle:

SELECT EXP(SUM(LOG(a))) AS product
FROM t ;

SELECT col, EXP(SUM(LOG(a))) AS product
FROM t 
GROUP BY col ;
share|improve this answer
2  
That's sweet. High school math has come back to haunt me. +1 !!! –  RolandoMySQLDBA Apr 30 '12 at 21:49
1  
Cool math, but I actually wanted to know how to create an aggregate function in general. product was just supposed to be one example of several. –  Matt Fenwick Apr 30 '12 at 23:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.