Axiom
Overview

sumif

Usage#

Syntax#

sumif(numeric_expression, condition)

Parameters#

  • numeric_expression: The numeric field or expression you want to sum.
  • condition: A boolean expression that determines which records contribute to the sum. Only the records that satisfy the condition are considered.

Returns#

sumif returns the sum of the values in numeric_expression for records where the condition is true. If no records meet the condition, the result is 0.

Use case examples#

In this use case, we calculate the total request duration for HTTP requests that returned a 200 status code.

Query

['sample-http-logs']
| summarize total_req_duration = sumif(req_duration_ms, status == '200')

Run in Playground

Output

total_req_duration
145000

This query computes the total request duration (in milliseconds) for all successful HTTP requests (those with a status code of 200).

In this example, we sum the span durations for the frontend service in OpenTelemetry traces.

Query

['otel-demo-traces']
| summarize total_duration = sumif(duration, ['service.name'] == 'frontend')

Run in Playground

Output

total_duration
32000

This query sums the span durations for traces related to the frontend service, providing insight into how long this service has been running over time.

Here, we calculate the total request duration for failed HTTP requests (those with status codes other than 200).

Query

['sample-http-logs']
| summarize total_req_duration_failed = sumif(req_duration_ms, status != '200')

Run in Playground

Output

total_req_duration_failed
64000

This query computes the total request duration for all failed HTTP requests (where the status code isn’t 200), which can be useful for security log analysis.

  • avgif: Computes the average of a numeric expression for records that meet a specified condition. Use avgif when you’re interested in the average value, not the total sum.
  • countif: Counts the number of records that satisfy a condition. Use countif when you need to know how many records match a specific criterion.
  • minif: Returns the minimum value of a numeric expression for records that meet a condition. Useful when you need the smallest value under certain criteria.
  • maxif: Returns the maximum value of a numeric expression for records that meet a condition. Use maxif to identify the highest values under certain conditions.

Other query languages#

Splunk SPL users

In Splunk SPL, the sumif equivalent functionality requires using a stats command with a where clause to filter the data. In APL, you can use sumif to simplify this operation by combining both the condition and the summing logic into one function.

Splunk example

| stats sum(duration) as total_duration where status="200"

APL equivalent

summarize total_duration = sumif(duration, status == '200')
ANSI SQL users

In ANSI SQL, achieving a similar result typically involves using a CASE statement inside the SUM function to conditionally sum values based on a specified condition. In APL, sumif provides a more concise approach by allowing you to filter and sum in a single function.

SQL example

SELECT SUM(CASE WHEN status = '200' THEN duration ELSE 0 END) AS total_duration
FROM http_logs

APL equivalent

summarize total_duration = sumif(duration, status == '200')

Updated

Was this page helpful?