TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
SQLite 3.30 FILTER: Separate payment totals by status in one query
Software Engineering · 2019
Software Engineering2019

SQLite 3.30 FILTER: Separate payment totals by status in one query

A reconciliation example using SQLite’s 2019 aggregate FILTER support with cancellations, settled payments and empty groups.

Archive retrospective · Topic year 2019

Yazılımcının GünlüğüPublished: Nov 18, 20263 min read
Reading progress0%

Apply different aggregate conditions to the same rows

SQLite 3.30.0 added the FILTER clause on aggregate functions in October 2019. A reconciliation report may need settled, cancelled and pending amounts for one month. Restricting a global WHERE clause to settled transactions removes the others. An aggregate filter gives each calculation its own condition. Keep the date basis and currency dimensions consistent before comparing those measures.

Keep no data separate from a zero amount

COUNT can return zero when no rows match while SUM can return NULL. Decide with finance how to display those cases. An actual zero amount, an empty dataset and an account that is still pending can require different labels. Storing values in the currency’s smallest unit can make calculation easier to check if the schema guarantees that unit. Preserve currency groupings.

Database access flowConceptual sketch · summary of the article's technical flow

Test which entries each result includes

Create successful, failed and refunded cases for a branch. Add another currency and a record without a date. List the expected identifiers for each metric before running the query. Compare grouped totals with transaction details. Verify the SQLite version that ships with the application because embedded platforms can keep an older engine after the development machine moves forward.

Compare equivalent query shapes

A more readable FILTER query is not always faster. When performance matters, compare it with a CASE expression on the same indexes and data. A large date range can still be expensive. If results disagree, check the date, currency and status rules before interpreting timings. The change should preserve business totals and make the query easier to audit.

Define the data contract first

SQL changes need explicit keys, uniqueness rules, null semantics, time zones and transaction boundaries. Test retries and concurrent workers, not only a single successful request. Count source and destination records before migrations, retain checkpoints and keep a rollback path. Strong data contracts prevent duplicated or missing records when jobs are repeated.

Measure execution plans under representative load

An index is not a universal speed improvement. Inspect query plans, rows read, statistics freshness and write overhead together. A query made faster for one dashboard may slow down frequent ingestion. Compare latency percentiles and system resource use under realistic data distribution before calling the optimization successful.

Be the first reader ✎ Write a comment

Comments

No comments yet. Be the first to contribute.

Jamie Collins

Reading “SQLite 3.30 FILTER: Separate payment totals by status in one query” raised this question for me: a small reproducible performance comparison would help.

Sample editor reply

Editor note: Results can vary by scale. Cost, latency and user experience should be compared together.

Sam Carter

The learning curve and accessibility also matter for actual users.

Chris Green

A practical case study with load testing or a pilot would help establish the trade-offs.

✎ Write a comment

Your first and last name will appear publicly alongside your comment.