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:

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

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

1
/tmp % echo 'year,month,revenue
2
2019,1,110
3
2019,1,30
4
2019,2,34
5
2019,2,112
6
2020,1,40
7
2020,1,50
8
2020,2,110' | sqlite-utils insert data.db invoices - --csv
9
/tmp % sqlite-utils rows data.db invoices
10
[{"year": "2019", "month": "1", "revenue": "110"},
11
 {"year": "2019", "month": "1", "revenue": "30"},
12
 {"year": "2019", "month": "2", "revenue": "34"},
13
 {"year": "2019", "month": "2", "revenue": "112"},
14
 {"year": "2020", "month": "1", "revenue": "40"},
15
 {"year": "2020", "month": "1", "revenue": "50"},
16
 {"year": "2020", "month": "2", "revenue": "110"}]

And the results of that query:

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