SQLite aggregate filter clauses

SQLite supports aggregate filter clauses (as of 3.30.0, released 2019-10-04), as described in this SQL Pivot in all databases tutorial.

An example query:

select
  year,
  sum(revenue) filter (where month = 1) as jan_revenue,
  sum(revenue) filter (where month = 2) as feb_revenue
from invoices
group by year

Here's an example using sqlite-utils to initially populate a database table:

/tmp % echo 'year,month,revenue
2019,1,110
2019,1,30
2019,2,34
2019,2,112
2020,1,40
2020,1,50
2020,2,110' | sqlite-utils insert data.db invoices - --csv
/tmp % sqlite-utils rows data.db invoices
[{"year": "2019", "month": "1", "revenue": "110"},
 {"year": "2019", "month": "1", "revenue": "30"},
 {"year": "2019", "month": "2", "revenue": "34"},
 {"year": "2019", "month": "2", "revenue": "112"},
 {"year": "2020", "month": "1", "revenue": "40"},
 {"year": "2020", "month": "1", "revenue": "50"},
 {"year": "2020", "month": "2", "revenue": "110"}]

And the results of that query:

/tmp % sqlite-utils data.db 'select
  year,
  sum(revenue) filter (where month = 1) as jan_revenue,
  sum(revenue) filter (where month = 2) as feb_revenue
from invoices
group by year' -t
  year    jan_revenue    feb_revenue
------  -------------  -------------
  2019            140            146
  2020             90            110

Related

• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• sqlite SQLite can use more than one index for a query - 2022-12-11
• sql Cumulative total over time in SQL - 2021-09-13
• sqlite List all columns in a SQLite database - 2020-05-06
• sqlite Lag window function in SQLite - 2020-04-19
• sqlite One-liner for running queries against CSV files with SQLite - 2022-06-20
• googlecloud Analyzing Google Cloud spend with Datasette - 2022-08-16
• sqlite Combining CTEs and VALUES in SQLite - 2023-01-29
• sqlite Null case comparisons in SQLite - 2020-04-21

Created 2021-08-04T14:08:58-07:00, updated 2021-08-13T12:01:05-07:00 · History · Edit