Beacon Robotics wants to know not just the average salary in a department, but how spread out those salaries actually are around that average, a tightly clustered department and a widely varied one can share the exact same AVG() while looking nothing alike. Variance measures that spread.
Learning Objectives
- Define variance, and explain the real difference between its population and sample forms.
- Compute variance under each database's real function name, including SQL Server's genuinely different naming.
- Know that SQLite has no built-in variance function at all, and the plain formula that replaces it.
Group Rows
One group per department
Average Squared Distance
From the mean, per value
Variance
Population or sample form
Population vs Sample Variance
Population variance (VAR_POP) treats the group as the entire population being measured, dividing by the count of values, N. Sample variance (VAR_SAMP) treats the group as a sample drawn from some larger population, dividing by N - 1 instead, a standard statistical correction (Bessel's correction) that makes a small sample's estimate less biased. The two numbers are only ever equal when N is infinite, in practice VAR_SAMP is always slightly larger.
The Real Function Names, by Database
| Database | Population Variance | Sample Variance |
|---|---|---|
| MySQL / MariaDB / PostgreSQL / Oracle | VAR_POP(col) | VAR_SAMP(col) |
| SQL Server | VARP(col) | VAR(col) — no underscore naming at all, a genuinely different convention |
| SQLite | No built-in function | No built-in function |
SQLite: No Built-In Variance at All
This is the mathematical identity variance is built on, the average of the squared values minus the square of the average, computed directly since SQLite's core engine has no VAR_POP, VAR_SAMP, or VARIANCE function to call.
SELECT AVG(salary * salary) - AVG(salary) * AVG(salary) AS population_variance FROM employees WHERE department_id = 3;
Try It Yourself
Compute the sample variance of salaries in department id 3, on SQL Server.
SELECT VAR(salary) AS sample_variance FROM employees WHERE department_id = 3;
Frequently Asked Questions
Which form, population or sample, should actually be used?
If the group genuinely is every row that matters, the whole population, VAR_POP is correct. If the group is a sample standing in for a larger, not-fully-observed population, VAR_SAMP's N-1 correction is the statistically appropriate one. Most real business queries, counting every actual employee in a department rather than a sample of them, technically call for VAR_POP, even though VAR_SAMP is the more commonly reached-for default out of habit.
What does a variance of zero mean?
Every value in the group is identical, there's no spread at all around the average.
Common Beginner Mistakes
Writing VAR_POP or VAR_SAMP on SQL Server
Neither name exists there, SQL Server uses VARP() for population variance and VAR() for sample variance instead, a genuinely different naming convention, not just a different spelling.
Expecting SQLite to have any built-in variance function
It doesn't, not VAR_POP, VAR_SAMP, nor a plain VARIANCE, the formula AVG(col*col) - AVG(col)*AVG(col) is the real, portable way to compute population variance there directly in plain SQL.
Best Practices
- Be explicit about population vs sample, don't rely on an unqualified VARIANCE name defaulting consistently across databases.
- On SQL Server, remember VARP is population and VAR is sample, the P suffix, not an underscore, marks which is which.
- On SQLite, wrap the manual variance formula in a reusable view or query template rather than retyping it, it's easy to get the squaring order wrong under pressure.
Interview Questions
What's the real difference between VAR_POP and VAR_SAMP?
VAR_POP divides the sum of squared deviations from the mean by N, the group's full count, treating it as the entire population. VAR_SAMP divides by N - 1 instead, a standard correction (Bessel's correction) that reduces bias when the group is actually a sample representing a larger, unobserved population. VAR_SAMP's result is always slightly larger for the same data.
How would you compute population variance on SQLite, which has no built-in function for it?
AVG(col * col) - AVG(col) * AVG(col), the average of the squared values minus the square of the average, the plain mathematical identity variance is defined by, computed directly in SQL with functions SQLite already has.
Summary
Variance measures spread around the average, with a real, meaningful population-vs-sample distinction (dividing by N vs N - 1). VAR_POP/VAR_SAMP is the shared name on four of these six databases, SQL Server genuinely uses VARP/VAR instead, and SQLite has no built-in version at all, needing the plain AVG(col*col) - AVG(col)*AVG(col) formula.
What's Next?
Standard deviation, variance's square root, with its own real naming split, and a genuine gotcha: the same unqualified function name means population on some databases and sample on others.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →