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
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.
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.
Comments
No comments yet. Be the first to contribute.
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.
Editor note: Results can vary by scale. Cost, latency and user experience should be compared together.
The learning curve and accessibility also matter for actual users.
A practical case study with load testing or a pilot would help establish the trade-offs.
✎ Write a comment