Aggregates

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (city TEXT, units INTEGER)")
con.executemany("INSERT INTO t VALUES (?, ?)", [("Nairobi", 12), ("Nairobi", 7), ("Kisumu", 3)])
print(con.execute("SELECT city, COUNT(*), SUM(units) FROM t GROUP BY city").fetchall())
con.close()

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (units INTEGER)")
con.executemany("INSERT INTO t VALUES (?)", [(12,), (7,), (3,)])
print(con.execute("SELECT AVG(units), MIN(units), MAX(units) FROM t").fetchone())
con.close()

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (city TEXT)")
con.executemany("INSERT INTO t VALUES (?)", [("Nairobi",), ("Nairobi",), ("Kisumu",)])
print(con.execute("SELECT COUNT(DISTINCT city) FROM t").fetchone())
con.close()

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (price REAL)")
con.execute("INSERT INTO t VALUES (10.5)")
con.execute("INSERT INTO t VALUES (NULL)")
print(con.execute("SELECT AVG(price), COUNT(price), COUNT(*) FROM t").fetchone())
con.close()