0

Any guidance an producing a UDF aggregate function so that when used in a grouping query, it will return results based on how many values in the group. I know the aggregate function needs to return an int but not sure how to pass the values (which vary) into it... Not a scalar type and not sure if and how it would constitute a tabular type.

E.g how would I build a udf function for average...

David Jones
  • 83
  • 8
  • Take a look at this: https://stackoverflow.com/questions/4374709/custom-aggregate-function-concat-in-sql-server – Amir Molaei Mar 01 '19 at 08:08
  • 2
    "based on how many values in the group" - you mean `COUNT(*)`? – Damien_The_Unbeliever Mar 01 '19 at 08:10
  • 1
    You can not use T-SQL to write custom aggregates (which is what "average" is) - you can only write them using C# (or any other CLR language) –  Mar 01 '19 at 10:31
  • @a_horse_with_no_name - you taught me something new today. I was recently trying to do what custom aggregates do using T-SQL and failed. I had never heard of these until your post. – Alan Burstein Mar 01 '19 at 14:33

1 Answers1

0

You can create UDF aggregates in c#.

A good start for reading is here: https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregates?view=sql-server-2017

coding Bott
  • 4,287
  • 1
  • 27
  • 44