SQLite aggregate functions

SQLite has several built-in aggregate functions.

Here I cover:

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM

SQLite AVG

Calculates the average value of all non-NULL values within a group.

SELECT
  AVG(val)
FROM
  sales
WHERE
  orderqty > 10;

SQLite COUNT

Counts the number of entries / rows in the queried dataset. Below SQLite SELECT query counts the number of customer rows in the customers table.

SELECT 
  COUNT(*)
FROM 
  customers;

SQLite MAX

Returns the maximum value in a list of values. Below query returns the maximum order quantity contained by the orderqty column in the sales table.

SELECT 
  MAX(orderqty)
FROM sales;

SQLite MIN

Returns the minimum value in a list of values. Below query returns the minimum order quantity contained by the orderqty column in the sales table.

SELECT
  MIN(orderqty)
FROM
  sales;

SQLite SUM

Returns the aggregate sum of a list of values. Below SQLite SELECT statement returns the total sum of sales revenue for all orders with a minimum order quantity of 10:

SELECT
  SUM(val)
FROM
  sales
WHERE
  orderqty > 10;