Among the 5,247 adults in NHANES 2017 to 2018 who had their blood pressure measured, the mean systolic pressure is 126.36 mm Hg: 127.41 for men and 125.37 for women. Exploring Data with Groups and Pivot Tables computes these numbers in pandas from a table built in memory. The same numbers come out of the database with one query:
SELECT p.sex, COUNT(*) AS adults, ROUND(AVG(b.sbp), 2) AS mean_sbp
FROM person AS p JOIN person_bp AS b USING (seqn)
WHERE p.age >= 18 AND b.sbp IS NOT NULL
GROUP BY p.sex;
| sex | adults | mean_sbp |
|---|---|---|
| female | 2,700 | 125.37 |
| male | 2,547 | 127.41 |
The query computes in the database, where the data live, and sends back two rows instead of thousands. That is the reason to learn SQL's aggregates even when pandas is at hand: summaries are often the only thing an analysis needs from a large table, and computing them where the table is stored avoids moving the table. This article covers the functions that compute such summaries, the GROUP BY and HAVING clauses that apply them group by group, and the window functions that compute group-wise values without collapsing the rows. The join in the query above, which attaches each person's blood pressure to their sex and age, is explained in Join Queries; here it is simply the way the two tables are combined.
Scalar functions
A scalar function takes values from one row and returns one value for that row; an aggregate function takes a set of rows and returns one value for the set. The scalar functions are mostly familiar from other languages, and they are where SQL dialects differ most, in names and in behavior.
| Expression | SQLite result | Note |
|---|---|---|
ROUND(16.25, 1) |
16.3 | Python's round(16.25, 1) gives 16.2 |
ROUND(-2.5) |
-3 | Python's round(-2.5) gives -2 |
ABS(-5) |
5 | |
LENGTH('ATORVASTATIN') |
12 | characters |
LOWER('ATORVASTATIN') |
atorvastatin | |
SUBSTR('E78.0', 1, 3) |
E78 | positions start at 1, not 0 |
log(100) |
2 | base 10 in SQLite and PostgreSQL |
ln(100) |
4.605 | natural logarithm |
sqrt(-1) |
NULL | a domain error gives NULL |
Rounding is the first trap. SQLite rounds halves away from zero; Python rounds them to the nearest even digit, so the same data rounded in the database and in pandas can disagree in the last place. Logarithms are the second: SQLite's math functions document that "SQLite works like PostgreSQL in that the log() function computes a base-10 logarithm", while "most other SQL database engines compute a natural logarithm for log()" (SQLite, n.d.-d). A query moved from MySQL or SQL Server to PostgreSQL or SQLite that uses log silently changes its answers by a factor of about 2.3. The math functions also return NULL for a domain error, such as the square root of a negative number, rather than raising one, and in SQLite they exist only when the library is compiled with them, as it is in the Python used here.
String functions extract and reshape text. ICD-10-CM diagnosis codes begin with a letter that names a chapter of the classification, so SUBSTR(icd10, 1, 1) groups the 15,565 reasons recorded for prescriptions by chapter: 3,273 are in chapter I (diseases of the circulatory system, mostly hypertension), 3,027 in E (endocrine and metabolic, mostly diabetes and high cholesterol), 1,479 in F (mental and behavioral disorders) and 1,475 in R (symptoms and signs).
Date functions differ between dialects more than any others. SQLite has no date type and stores dates as text, numbers of days or seconds; its functions date, julianday and strftime work on all three: julianday('2018-12-31') - julianday('2017-01-01') is 729 days, and strftime('%Y', '2018-06-30') extracts the year (SQLite, n.d.-f). MySQL has DATEDIFF, YEAR and CURDATE, where DATEDIFF(a, b) is the number of days from b to a (MySQL, n.d.-a); PostgreSQL subtracts dates directly and has EXTRACT and date_part; and SQL Server's DATEDIFF(day, start, end) takes the unit first and the dates in the opposite order (Microsoft, n.d.-b). Date arithmetic is one of the few places where a query almost always has to be rewritten when it moves between systems.
Aggregate functions and NULL
The standard aggregates are COUNT, SUM, AVG, MIN and MAX. Without GROUP BY they summarize every row that passes the WHERE clause into a single row.
COUNT
COUNT comes in three forms that answer different questions:
SELECT COUNT(*) AS rows_, COUNT(chol) AS chol_values,
COUNT(hba1c) AS hba1c_values, COUNT(DISTINCT chol) AS distinct_chol
FROM lab;
| rows_ | chol_values | hba1c_values | distinct_chol |
|---|---|---|---|
| 7,435 | 6,738 | 6,045 | 253 |
COUNT(*) counts rows: 7,435 people have a row in the laboratory table. COUNT(chol) counts non-NULL values of the column: 6,738 have a total cholesterol result. COUNT(DISTINCT chol) counts distinct non-NULL values: cholesterol is reported in whole milligrams per deciliter and takes 253 different values. The SQLite documentation defines the first two exactly this way, count(X) as "the number of times that X is not NULL in a group" and count(*) as "the total number of rows in the group" (SQLite, n.d.-a). Reporting COUNT(*) beside COUNT(column) is the quickest check of how much a summary rests on.
SUM, AVG and the denominator
Every aggregate except COUNT(*) ignores NULLs. For AVG this means the mean of the values present, which is usually what is wanted, but the alternatives are easy to compute by accident:
| Expression | Result (mg/dL) | Divides by |
|---|---|---|
AVG(chol) |
179.89 | 6,738 values |
SUM(chol) * 1.0 / COUNT(chol) |
179.89 | 6,738 values |
SUM(chol) * 1.0 / COUNT(*) |
163.03 | 7,435 rows |
AVG(COALESCE(chol, 0)) |
163.03 | 7,435 rows, NULLs as 0 |
The last two treat the 697 people without a result as having a cholesterol of zero and pull the mean down by 17 mg/dL. Writing SUM(x) / COUNT(*) by hand, or replacing NULLs with zero "to be safe", is a common way to get this wrong.
The converse problem is a value that is not NULL but should be. The blood pressure table records a diastolic pressure of 0 in 394 of its 19,959 readings, a value the survey documentation allows but which the earlier articles on these data treat as missing (Cleaning Data). AVG(dbp) over all readings is 68.31 mm Hg; AVG(NULLIF(dbp, 0)), which turns the zeros into NULLs that AVG then skips, is 69.68. A summary is only as good as the agreement between what NULL means in the data and what the analysis needs it to mean.
Integer division in a proportion
The share of adults who currently smoke is the number of current smokers divided by the number of adults asked:
SELECT SUM(CASE WHEN smq040 IN (1, 2) THEN 1 ELSE 0 END) / COUNT(*) AS integer_ratio,
SUM(CASE WHEN smq040 IN (1, 2) THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS proportion,
AVG(CASE WHEN smq040 IN (1, 2) THEN 1.0 ELSE 0.0 END) AS proportion_avg
FROM smoking
WHERE smq020 IN (1, 2);
The first column is 0. Both the sum (1,021) and the count (5,856) are integers, and SQLite, PostgreSQL and SQL Server divide integers by truncating (Simple SQL Queries). Multiplying by 1.0 first gives 0.1744, and so does the third form, which is worth remembering: the mean of a 0 and 1 indicator is the proportion of ones. A related difference between systems: AVG of an integer column returns a real number in SQLite and a numeric in PostgreSQL (PostgreSQL Global Development Group, n.d.-a), but an int in SQL Server (Microsoft, n.d.-a), so there an indicator written with 1 and 0 averages to 0, and the 1.0 and 0.0 of the third form above are needed.
Aggregates over no rows
An aggregate query without GROUP BY returns exactly one row even when no row passes the WHERE clause. For a condition no one meets, such as a cholesterol above 1,000, COUNT(*) and COUNT(chol) are 0 but SUM, AVG, MIN and MAX are all NULL. That SUM of nothing is NULL rather than 0 is required by the standard; SQLite provides a non-standard TOTAL() that returns 0.0 instead, noting that "NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it" (SQLite, n.d.-a). Code that adds up sums from several queries should expect NULLs, or wrap them in COALESCE(SUM(x), 0).
GROUP BY
GROUP BY partitions the rows that pass WHERE into groups with equal values of the grouping expressions, and the aggregates are then computed once per group. This is the split-apply-combine pattern of Exploring Data with Groups and Pivot Tables, and Wickham (2011) names SQL's GROUP BY as one of its implementations.
The query at the top grouped by sex. Grouping by two expressions gives one row per combination, and reproduces the pivot table of that article exactly:
SELECT p.sex,
CASE WHEN p.age < 40 THEN '18-39' WHEN p.age < 60 THEN '40-59' ELSE '60+' END AS age_group,
COUNT(*) AS adults, ROUND(AVG(b.sbp), 2) AS mean_sbp
FROM person AS p JOIN person_bp AS b USING (seqn)
WHERE p.age >= 18 AND b.sbp IS NOT NULL
GROUP BY p.sex, age_group
ORDER BY p.sex, age_group;
| sex | 18-39 | 40-59 | 60+ |
|---|---|---|---|
| female | 110.83 (921) | 126.18 (836) | 138.85 (943) |
| male | 119.31 (824) | 126.49 (743) | 134.93 (980) |
(The query returns six rows; they are arranged here as a table, with the number of adults in parentheses.) Men's pressure is higher among young adults and women's among the oldest, the crossing that the pandas article discusses.
Two levels of aggregation
person_bp in these queries is a view, a stored query that can be used like a table. It reduces the readings to one row per person:
CREATE VIEW person_bp AS
SELECT seqn,
AVG(sbp) AS sbp,
AVG(NULLIF(dbp, 0)) AS dbp,
COUNT(sbp) AS sbp_readings
FROM bp_reading
WHERE reading <= 3
GROUP BY seqn;
Each adult's systolic pressure is the mean of up to three readings, the definition used in the pandas articles. (A fourth reading, taken when one of the first three was interrupted, exists for 556 people; leaving it out is what makes the numbers match.) The view makes a real difference. Averaging the 15,146 individual readings of the same adults directly, without first averaging within each person, gives 126.01, not 126.36. The two are different means: the first weights each reading equally, the second each person. The adults with fewer than three readings, 78 with one and 439 with two, have higher pressures on average (134.82 and 135.55) than the 4,730 with three (125.37), so weighting by readings shifts the mean. Which mean is right depends on whether the unit of analysis is the person or the measurement, and in a one-to-many table the choice has to be made explicitly. Aggregating in two steps, first to the unit and then across units, is the general pattern; Subqueries shows how to write it without a view.
NULL keys form a group
Rows whose grouping value is NULL are not dropped. The SQLite documentation states that "For the purposes of grouping rows, NULL values are considered equal" (SQLite, n.d.-e), so grouping people by education gives a NULL group of 3,685, the people under 20 who were not asked, beside the five education levels and the two refusal codes. pandas' groupby drops NULL keys by default, so the same grouping in SQL and in pandas returns different numbers of groups unless dropna=False is given.
HAVING
WHERE filters rows before grouping; HAVING filters groups after it, and can therefore test aggregates. The drugs taken by at least 300 people:
SELECT d.name, COUNT(DISTINCT p.seqn) AS people
FROM prescription AS p JOIN drug AS d USING (drug_id)
GROUP BY d.name
HAVING COUNT(DISTINCT p.seqn) >= 300
ORDER BY people DESC;
| name | people |
|---|---|
| ATORVASTATIN | 593 |
| METFORMIN | 570 |
| LISINOPRIL | 498 |
| AMLODIPINE | 451 |
| METOPROLOL | 433 |
| LEVOTHYROXINE | 404 |
| OMEPRAZOLE | 324 |
| LOSARTAN | 318 |
| SIMVASTATIN | 305 |
The list is the medicine cabinet of an aging population: a statin, a diabetes drug, four drugs for blood pressure, a thyroid hormone and an acid reducer. At the other end, 166 of the 666 drugs in use are reported by a single person. COUNT(DISTINCT p.seqn) counts people rather than prescriptions, which differ because some people report the same drug twice (Relational Databases).
The rule for where a condition goes follows from the order of evaluation. A condition on individual rows (age >= 20, chol >= 240) belongs in WHERE, where it also reduces the work of grouping. A condition on a group's aggregate (COUNT(*) > 250) can only go in HAVING, because the aggregate does not exist until the groups are formed. Among adults aged 20 and over, 292 women and 226 men have a total cholesterol of 240 mg/dL or more; HAVING COUNT(*) > 250 keeps only the women. Since version 3.39.0, SQLite also accepts HAVING without GROUP BY, treating the whole input as one group: SELECT COUNT(*) FROM lab WHERE chol > 1000 HAVING COUNT(*) > 0 returns no row at all, where the same query without HAVING returns one row containing 0 (SQLite, n.d.-c).
The logical order of a query
With grouping, the full sequence in which a query's clauses take effect is:
FROMand its joins assemble the input rows.WHEREkeeps the rows for which its condition is true.GROUP BYforms the groups.HAVINGkeeps the groups for which its condition is true.SELECTcomputes the output columns, aggregates and window functions;DISTINCTthen removes duplicate rows.ORDER BYsorts.LIMITandOFFSETcut.
The SQLite documentation describes the evaluation of a SELECT in this order, with the caution, quoted in Simple SQL Queries, that it is a description of the result and not of the procedure (SQLite, n.d.-e). The order explains several rules at once: an aggregate cannot appear in WHERE (step 2 comes before groups exist); an alias from SELECT can be used in ORDER BY (step 6 follows step 5) but not, in standard SQL, in WHERE or HAVING; window functions, computed at step 5, can rank the results of grouping but cannot be filtered in the same query's WHERE.
Bare columns
Standard SQL requires every column in the SELECT list of a grouped query to be either a grouping expression or inside an aggregate. A query such as SELECT race, age, COUNT(*) FROM person GROUP BY race has no meaning in the standard, because each race group contains people of many ages and the query does not say which age to show. PostgreSQL and SQL Server reject it (PostgreSQL Global Development Group, n.d.-b; Microsoft, n.d.-c), and so does MySQL, whose ONLY_FULL_GROUP_BY mode is on by default (MySQL, n.d.-b). PostgreSQL and MySQL relax the rule only when the extra column is functionally dependent on the grouping columns, as when the table's primary key is grouped on.
SQLite accepts it. The documentation calls such a column a "bare column" and states that its value comes from "one of the input rows that form the aggregate", unpredictably (SQLite, n.d.-e). The query above returns ages of 18, 57, 2, 66, 2 and 13 for the six groups, which are simply the ages of whichever row SQLite happened to use. There is one deliberate use: when the query has a single MIN or MAX, the bare columns come from a row where that extreme occurs. SELECT seqn, MAX(bmi) FROM exam returns person 97938 with the largest BMI, 86.2. But with ties the row is again arbitrary, SELECT race, MAX(age), seqn ... GROUP BY race names one of the many 80-year-olds in each group, and the documentation knows of no other SQL implementation that chooses the row this way (SQLite, n.d.-b). Portable SQL finds the row with the maximum through a subquery or a window function.
Statistics beyond the basic five
The SQL standard defines aggregates for the sample variance and standard deviation, VAR_SAMP and STDDEV_SAMP, which PostgreSQL, MySQL and Oracle provide under those or similar names, and ordered-set aggregates such as PERCENTILE_CONT for medians and quantiles (PostgreSQL Global Development Group, n.d.-a). SQLite's built-in set stops at the basic five, TOTAL and the string aggregate group_concat; median and percentile are available only from version 3.51.0 and only when compiled in (SQLite, n.d.-a). The SQLite in this Python has neither (no such function: median). Both can be computed from the basic aggregates.
The sample standard deviation of $n$ values $x_i$ with mean $\bar{x}$ is
$$s = \sqrt{\frac{\sum_i (x_i - \bar{x})^2}{n-1}} = \sqrt{\frac{\sum_i x_i^2 - \left(\sum_i x_i\right)^2 / n}{n-1}},$$
where the second form follows by expanding the square and using $\sum_i x_i = n\bar{x}$. The second form needs one pass over the data:
SELECT sqrt((SUM(chol * chol) - SUM(chol) * SUM(chol) / COUNT(chol)) / (COUNT(chol) - 1)) AS sd
FROM lab;
It gives 40.60 mg/dL for cholesterol, agreeing with Python's statistics.stdev to six decimals. The one-pass form subtracts two large, nearly equal numbers and can lose precision badly when the mean is large relative to the spread; the two-pass form, which first computes the mean in a subquery and then sums squared deviations from it, does not, and gives the same 40.60 here.
A median needs the middle value of the sorted data, or the mean of the two middle values when the count is even. With n values, skipping $\lfloor (n-1)/2 \rfloor$ of them and keeping one (odd $n$) or two (even $n$) does it:
SELECT AVG(chol) AS median FROM (
SELECT chol FROM lab WHERE chol IS NOT NULL ORDER BY chol
LIMIT 2 - (SELECT COUNT(chol) FROM lab) % 2
OFFSET ((SELECT COUNT(chol) FROM lab) - 1) / 2);
The median cholesterol is 176 mg/dL, below the mean of 179.89, a sign of the right skew usual in laboratory values. These are workarounds; for anything beyond a few summary statistics, it is simpler to read the rows into pandas (Queries in Python) and use its methods.
Window functions
GROUP BY collapses each group to one row. A window function computes a value over a set of rows related to the current row, its window, and returns it on every row, so the rows remain. It is written as a function followed by OVER (...), in which PARTITION BY divides the rows into groups and ORDER BY orders them within each group. Window functions are part of the SQL standard and entered SQLite in version 3.25.0 (2018); the SQLite developers used PostgreSQL's documentation as their reference and tested against it (SQLite, n.d.-g). They are the SQL counterpart of pandas' groupby(...).transform, rank and cumsum.
Ranks and ties
Ranking the drugs by the number of people taking them shows the three ranking functions and how they differ only at ties:
| name | people | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| TAMSULOSIN | 129 | 22 | 22 | 22 |
| SERTRALINE | 127 | 23 | 23 | 23 |
| AMOXICILLIN | 126 | 25 | 24 | 24 |
| RANITIDINE | 126 | 24 | 24 | 24 |
| IBUPROFEN | 120 | 26 | 26 | 25 |
SELECT d.name, COUNT(DISTINCT p.seqn) AS people,
ROW_NUMBER() OVER (ORDER BY COUNT(DISTINCT p.seqn) DESC) AS row_number,
RANK() OVER (ORDER BY COUNT(DISTINCT p.seqn) DESC) AS rank,
DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT p.seqn) DESC) AS dense_rank
FROM prescription AS p JOIN drug AS d USING (drug_id)
GROUP BY d.name;
Amoxicillin and ranitidine tie at 126 people. ROW_NUMBER numbers rows consecutively and breaks the tie arbitrarily: here it gave ranitidine 24 and amoxicillin 25, though the result is listed alphabetically. RANK gives tied rows the same rank and leaves a gap after them, so ibuprofen is 26th; DENSE_RANK leaves no gap, so ibuprofen is 25th (SQLite, n.d.-g). The window functions are evaluated after GROUP BY, so they can rank aggregates, as here.
With PARTITION BY, the numbering restarts in each group, which answers "the top k in each group" questions. The two most common drugs in each age group:
| age group | first | second |
|---|---|---|
| 0-17 | albuterol (115) | amoxicillin (88) |
| 18-39 | ethinyl estradiol with norethindrone (36) | ethinyl estradiol with norgestimate (36) |
| 40-59 | metformin (159) | lisinopril (140) |
| 60+ | atorvastatin (449) | metformin (380) |
The query numbers the drugs within each age group with ROW_NUMBER() OVER (PARTITION BY age_group ORDER BY people DESC, name) and keeps rows numbered 1 and 2. In the 18 to 39 group two oral contraceptives tie at 36 people, and only the name in the ORDER BY decides which is "first"; RANK would have given both rank 1.
Running totals and the default frame
An aggregate used as a window function, such as SUM(...) OVER (ORDER BY ...), computes a running total. Cumulating prescriptions over drugs ordered from most to least common shows how concentrated prescribing is: atorvastatin alone accounts for 4.2% of the 14,300 prescriptions with an identified drug, the top ten for 29.5%, and the 31 most common drugs for half, out of 666 drugs in use.
The window's frame, the rows the function reads, has a default that surprises. With an ORDER BY and no frame stated, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes the current row's peers, all rows with the same ORDER BY value (SQLite, n.d.-g). A running count of adults ordered by age therefore does not count 1, 2, 3:
SELECT seqn, age,
COUNT(*) OVER (ORDER BY age) AS range_running,
COUNT(*) OVER (ORDER BY age ROWS UNBOUNDED PRECEDING) AS rows_running
FROM person WHERE age >= 18;
| seqn | age | range_running | rows_running |
|---|---|---|---|
| 93706 | 18 | 144 | 1 |
| 93712 | 18 | 144 | 2 |
| 93938 | 18 | 144 | 3 |
| 94001 | 18 | 144 | 4 |
All 144 eighteen-year-olds are peers, so each sees the whole group in its default frame and gets 144. ROWS frames count physical rows and give the expected 1, 2, 3, though the order among the tied rows is then arbitrary unless the ORDER BY includes a tie-breaker. The running totals of prescriptions above state their frame as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and order by count and name for this reason.
Comparing each row with its group
An aggregate over a partition, with no ORDER BY, places the group's value on every row. Each adult's systolic pressure can then be compared with the mean of adults of the same sex and decade of age:
SELECT p.seqn, p.sex, p.age, b.sbp,
AVG(b.sbp) OVER (PARTITION BY p.sex, p.age / 10) AS group_mean,
b.sbp - AVG(b.sbp) OVER (PARTITION BY p.sex, p.age / 10) AS difference
FROM person AS p JOIN person_bp AS b USING (seqn)
WHERE p.age >= 18 AND b.sbp IS NOT NULL
ORDER BY p.seqn;
The first adult in the file, a 66-year-old woman with a mean systolic pressure of 202 mm Hg, is 67.12 above the mean of 134.88 for women in their sixties; 558 of the 5,247 adults are at least 20 mm Hg above the mean of their group. With GROUP BY, the group means would be available only as separate rows to be joined back; the window function computes them in place, as pandas' transform does.
In Python
import sqlite3
import pandas as pd
con = sqlite3.connect("nhanes.db")
by_group = pd.read_sql("""
SELECT p.sex, COUNT(*) AS adults, AVG(b.sbp) AS mean_sbp
FROM person AS p JOIN person_bp AS b USING (seqn)
WHERE p.age >= 18 AND b.sbp IS NOT NULL
GROUP BY p.sex""", con)
# the same in pandas, from the rows
rows = pd.read_sql("""SELECT p.sex, b.sbp FROM person AS p JOIN person_bp AS b USING (seqn)
WHERE p.age >= 18 AND b.sbp IS NOT NULL""", con)
rows.groupby("sex").sbp.agg(["size", "mean"])
The first form moves two rows from the database; the second moves 5,247 and summarizes them in Python. Both give the same means. The numbers in this article are computed by tools/experiments/data-science/sql-aggregate-functions.py in the site's repository, and the figure by tools/figures/sql-aggregate-functions.py.
References
- Microsoft (n.d.-a). AVG (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql record
- Microsoft (n.d.-b). DATEDIFF (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql record
- Microsoft (n.d.-c). GROUP BY (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql record
- MySQL (n.d.-a). Date and time functions. MySQL 8.4 Reference Manual. https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html record
- MySQL (n.d.-b). MySQL handling of GROUP BY. MySQL 8.4 Reference Manual. https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html record
- PostgreSQL Global Development Group (n.d.-a). Aggregate functions. PostgreSQL documentation. https://www.postgresql.org/docs/current/functions-aggregate.html record
- PostgreSQL Global Development Group (n.d.-b). SELECT. PostgreSQL documentation. https://www.postgresql.org/docs/current/sql-select.html record · 4 pages
- SQLite (n.d.-a). Built-in aggregate functions. https://sqlite.org/lang_aggfunc.html record
- SQLite (n.d.-b). Quirks, caveats, and gotchas in SQLite. https://sqlite.org/quirks.html record · 3 pages
- SQLite (n.d.-c). Release 3.39.0 on 2022-06-25. https://sqlite.org/releaselog/3_39_0.html record · 3 pages
- SQLite (n.d.-d). Built-in mathematical SQL functions. https://sqlite.org/lang_mathfunc.html record
- SQLite (n.d.-e). The SELECT statement. https://sqlite.org/lang_select.html record · 5 pages
- SQLite (n.d.-f). Date and time functions. https://sqlite.org/lang_datefunc.html record
- SQLite (n.d.-g). Window functions. https://sqlite.org/windowfunctions.html record
- Wickham, H. (2011). The split-apply-combine strategy for data analysis. Journal of Statistical Software, 40(1), 1–29. https://doi.org/10.18637/jss.v040.i01 record · 3 pages