Telemetry
Browse docs
Discussion TopicsUpdated July 28, 2026Reviewed by the Telemetry product team2 min read

Use this doc with your coding agent

Copy a Telemetry prompt and ask Claude Code, Codex, or Cursor to instrument the workflow covered here.

PromptsGet API key
On this page
  1. Count outcomes and protect the denominator
  2. Choose the population deliberately
  3. Rates need operational context

SQL Conditional Aggregation

Conditional aggregation answers several related questions in one grouped query. Instead of scanning a table once for successful requests and again for failed requests, put the condition inside a CASE expression and aggregate both outcomes together. This pattern is useful for error rates, conversion rates, retry rates, cache hit rates, and any result where the denominator must remain visible.

Count outcomes and protect the denominator

SELECT
  route_template,
  COUNT(*) AS requests,
  SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS server_errors,
  100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS server_error_rate_pct
FROM api_request_events
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY route_template
HAVING COUNT(*) >= 20
ORDER BY server_error_rate_pct DESC;

The total, conditional count, and percentage tell different parts of the story. The total establishes sample size. The count shows absolute impact. The rate makes routes with different traffic volumes comparable. NULLIF protects a ratio from a zero denominator, while 100.0 keeps the result in percentage arithmetic.

Choose the population deliberately

The WHERE clause defines the population before grouping. The CASE expression defines which rows in that population match an outcome. That distinction matters: excluding failures in WHERE makes an error rate impossible, and including health checks in the denominator can make customer traffic look healthier than it is.

Use mutually exclusive conditions when every row should land in one status bucket. Use independent conditions when a row can legitimately satisfy more than one label. Always check whether the sum of the buckets is expected to equal the total.

Rates need operational context

A high percentage from two events is rarely equivalent to the same percentage from two million events. Keep the count in the result, use a reviewed minimum-volume rule, and compare complete time windows. Null status values, retries, duplicate deliveries, and late events also need explicit treatment.

Start with the tested API error-rate recipe, transaction rollback recipe, or conversion-rate guide. For other supported syntax, see the DataFusion SQL reference.