question

SUATSUPHI-1172 avatar image
0 Votes"
SUATSUPHI-1172 asked Bruce-SqlWork edited

how to use CreateAggregate for sum operator sqlite

Hi,

I get this error that SQLite cannot apply aggregate operator 'Sum' on expressions of type 'decimal'. Convert the values to a supported type, or use LINQ to Objects to aggregate the results on the client side.

I searhed it. I think it can be override using sqlitefunction. Cold you help about it. I found some example from Microsoft Learn

https://learn.microsoft.com/tr-tr/dotnet/standard/data/sqlite/user-defined-functions

But there is no example about SUM operator

  connection.CreateFunction(
      "volume",
      (double radius, double height)
          => Math.PI * Math.Pow(radius, 2) * height);

and this example exists


  connection.CreateFunction(
      "regexp",
      (string pattern, string input)
          => Regex.IsMatch(input, pattern));

and this example exists



 connection.CreateAggregate(
      "stdev",
        
      // A tuple to maintain context between rows
      (Count: 0, Sum: 0.0, SumOfSquares: 0.0),
        
      // This is called for each row
      ((int Count, double Sum, double SumOfSquares) context, double value) =>
      {
          context.Count++;
          context.Sum += value;
          context.SumOfSquares += value * value;
        
          return context;
      },
        
      // This is called to get the final result
      context =>
      {
          var variance = context.SumOfSquares - context.Sum * context.Sum / context.Count;
        
          return Math.Sqrt(variance / context.Count);
      });

Is it possible to write something like this for the sum operator? how ?

=======================================

 [SqliteFunctionAttribute(Name = "SUM", Arguments = 1, FuncType = FunctionType.Aggregate)]
      class SqliteSum : SqliteFunction {
          public override void Step(object[] args, int stepNumber, ref object contextData) {
              if (args.Length < 1 || args[0] == null || args[0] == DBNull.Value)
                  return;
              try {
                  decimal d = Math.Round(Convert.ToDecimal(args[0]), 4);
                  if (contextData != null) d += (Decimal)contextData;
                  contextData = d;
              } catch (Exception ex) {
                  WebServer.Log("Exception: {0}", ex);
              }
          }
        
          public override object Final(object contextData) {
              return contextData;
          }
      }







dotnet-aspnet-core-mvcdotnet-sqlite
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered Bruce-SqlWork edited

I don't believe mapping aggregate functions by attribute was ever implemented (it uses a different registering method). you should use the new syntax.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.