GetAggregateValueByWhereStatementAsync

Part of the IQSDataTable interface, this method computes a SQL aggregate value (such as SUM, AVG, COUNT, MAX or MIN) over the records that match a given WHERE condition.

You can use this method to efficiently calculate totals, averages, counts, or other aggregate metrics without having to retrieve all matching records and perform the calculations in memory.

The method takes a SQL‑style predicate to filter the rows, the name of the aggregate function to apply, and the target field on which the function operates. The result is returned as an object, which you can cast to the appropriate type based on the aggregate function used.

Supported aggregate functions include:
  • AVG: Calculates the average value of the specified field.
  • COUNT: Counts the number of rows that match the predicate.
  • COUNT_BIG: Similar to COUNT, but returns a bigint for larger counts.
  • SUM: Calculates the sum of the values in the specified field.
  • MIN: Finds the minimum value in the specified field.
  • MAX: Finds the maximum value in the specified field.
  • ABS: Returns the absolute value of the specified field.
  • CEILING: Rounds the value up to the nearest integer.
  • COS: Calculates the cosine of the value (in radians).
  • COT: Calculates the cotangent of the value (in radians).
  • DEGREES: Converts radians to degrees.
  • EXP: Calculates the exponential function (e^x).
  • FLOOR: Rounds the value down to the nearest integer.
  • LOG: Calculates the natural logarithm.
  • LOG10: Calculates the base-10 logarithm.
  • RADIANS: Converts degrees to radians.
  • SIGN: Returns the sign of the value (-1, 0, or 1).
  • SIN: Calculates the sine of the value (in radians).
  • SQRT: Calculates the square root of the value.
  • SQUARE: Squares the value.
  • TAN: Calculates the tangent of the value (in radians).
  • RAND: Generates a random number (typically between 0 and 1).

Asynchronously returns the result of the aggregate function applied to targetField for the filtered rows. If no rows satisfy the predicate, null is returned.

Signature

public async Task<object> GetAggregateValueByWhereStatementAsync(string predicate, string aggregateFunction, string targetField)

Parameters

NameTypeDescription
predicatestring The SQL WHERE clause (without the WHERE keyword) that filters which rows are considered. Example: "Status = 'Active'".
aggregateFunctionstring The SQL aggregate function to apply. Supported values: "SUM", "AVG", "COUNT", "MAX", "MIN".
targetFieldstring The column name on which the aggregate function operates. For COUNT you may pass "*".

Returns

A Task<object> representing the asynchronous operation. The result is the computed aggregate value (boxed as object) for the matching records. If no rows match the predicate, null is returned.

Note: Cast the returned value to the expected type, e.g. (decimal)result for SUM/AVG or (int)result for COUNT.

Example Usage

// Define the data table
IQSDataTable ordersTable = QSAppContext.Tables["Orders"];

// Total sales for active customers
decimal? totalSales = (decimal?)await ordersTable.GetAggregateValueByWhereStatementAsync(
    "Status = 'Active'", "SUM", "Amount"
);

// Average price of products in stock
decimal? avgPrice = (decimal?)await ordersTable.GetAggregateValueByWhereStatementAsync(
    "Stock > 0", "AVG", "Price"
);

// Number of pending orders
int? pendingCount = (int?)await ordersTable.GetAggregateValueByWhereStatementAsync(
    "Status = 'Pending'", "COUNT", "*"
);

string Sample = $"Total Sales: {totalSales ?? 0}";