How to use the Aggregate / Window Functions (sum, avg, …) in Sqlite ?

About

The aggregate / window function in Sqlite.

Sqlite supports the following aggregate /window function ¹⁾:

• SUM (total)
• AVG
• Max
• Min
• Rank
• row_number
• more see the Specification Window function page

Example

How to calculate a percentage / ratio over a grand total ?

You use a aggregate/window function and you apply the all partition with the syntax over ()

Example:

select 
  sold,
  (sold / sum(sold) OVER () * 100) as perc
from mySales

How to create bucket (statistics bin) ?

We sqlite you create bin (Statistics - (Discretizing|binning) (bin)) with the ntile function

select 
  orderNumber, 
  amountSold, 
  ntile(6) over (order by amountSold asc) as bin 
from 
  mySales 
order by amountSold desc