0

How do you create an Aggregate Function to get the total sum of a column for example? I know this is built in as the SUM() function but i need to make other more complex functions and this seems like a good place to start.

  • Is the use of cursor's necessary?
  • The parameter definition is the sames as for normal functions?
  • And what variable scope should i use?

I'm not concerned with speed or efficiency only with the most simple way to do it. Thank you for your time.

Carlos Martins
  • 43
  • 10
  • You may want to read this: https://dev.mysql.com/doc/refman/8.0/en/adding-udf.html – Thorsten Kettner Jun 16 '20 at 21:29
  • 3
    Why not tell us a bit more about what you're after? – Strawberry Jun 16 '20 at 21:30
  • It is extremely rare that the most simple way to manipulate data is to create a compiled udf. I agree with @Strawberry, you should describe what you are really after and we can suggest solutions. The solution may indeed be a compiled udf, but it is extremely likely that we can suggest some alternative solution using native sql functions. – Shadow Jun 16 '20 at 21:36
  • @strawberry i need to create several aggregate function for college but my professor only though us the basics, he translated [https://www.mysqltutorial.org/mysql-stored-function/](this) to our local language give it to us in a pdf. I need to recreate max(), min(), avg(), and a function that manipulates dates. – Carlos Martins Jun 16 '20 at 21:38
  • @Thorsten Kettner thank you. but this seems a bit to complicated I think we are supposed to do it only with SQL. Is there no other way ? if not i will just use the built in fuctions and wrap them with a diferent name and some extra steps. – Carlos Martins Jun 16 '20 at 21:44
  • This is nothing that can be done with SQL only. But, yes, you can of course combine aggregation functions or combine aggregation functions with other expressions. `COUNT(*)` and `SUM(1)`do the same thing. `SUM(value) / COUNT(value)` is the same as `AVG(value)`, `SUM(CASE WHEN value = 'YES' THEN 1 ELSE 0 END)` counts yesses, etc. – Thorsten Kettner Jun 16 '20 at 21:49
  • 2
    I don't think you can define aggregate functions using SQL, they can only be done as UDF: https://dev.mysql.com/doc/refman/8.0/en/adding-udf.html#udf-aggr-calling – Barmar Jun 16 '20 at 21:52
  • Are you using mysql or mariadb? The latter can create aggregate functions in sql as well. Mariadb is a fork of mysql, but it is not mysql. – Shadow Jun 16 '20 at 22:01

0 Answers0