sqlcmd.net validated sql reference

Command guide

advanced windowing MySQL MariaDB SQL Server PostgreSQL SQLite

Normalise Values to Z-Scores

Rescale each row's value to the number of standard deviations it sits above or below the mean using `(value − AVG … OVER ()) / STDDEV_SAMP … OVER ()`.

Created Last updated 0/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green

Example 1

Normalise three exam scores to z-scores

The three scores 10, 20, 30 have a mean of 20 and a sample standard deviation of 10. Alice's z-score is (10 − 20) / 10 = −1.0: one standard deviation below the mean. Bob's score equals the mean exactly, yielding 0.0. Carol is one standard deviation above, yielding 1.0. In practice you would add PARTITION BY group_col inside the OVER() clauses to compute separate z-scores within each group — for example, per subject or per exam sitting. Replace STDDEV_SAMP with STDDEV_POP (or STDEVP in SQL Server) if the dataset represents the full population rather than a sample.

MySQL MariaDB PostgreSQL
Engine-specific syntax
Source table data
Setup
CREATE TABLE test_scores (student VARCHAR(20), score INT);

INSERT INTO
  test_scores
VALUES
  ('Alice', 10),
  ('Bob', 20),
  ('Carol', 30);
Validated query
SQL
SELECT
  student,
  score,
  (score - AVG(score * 1.0) OVER ()) / STDDEV_SAMP(score) OVER () AS z_score
FROM
  test_scores
ORDER BY
  score;
Expected result
studentscorez_score
Alice10-1
Bob200
Carol301
SQL Server
Engine-specific syntax
Source table data
Setup
CREATE TABLE test_scores (student VARCHAR(20), score INT);

INSERT INTO
  test_scores
VALUES
  ('Alice', 10),
  ('Bob', 20),
  ('Carol', 30);
Validated query
SQL
SELECT
  student,
  score,
  (score - AVG(score * 1.0) OVER ()) / STDEV (score) OVER () AS z_score
FROM
  test_scores
ORDER BY
  score;
Expected result
studentscorez_score
Alice10-1
Bob200
Carol301
SQLite
Engine-specific syntax
Source table data
Setup
CREATE TABLE test_scores (student VARCHAR(20), score INT);

INSERT INTO
  test_scores
VALUES
  ('Alice', 10),
  ('Bob', 20),
  ('Carol', 30);
Validated query
SQL
WITH
  stats AS (
    SELECT
      AVG(score * 1.0) AS mean_score,
      SQRT(
        (
          SUM(score * score) - SUM(score) * SUM(score) * 1.0 / COUNT(*)
        ) / (COUNT(*) - 1)
      ) AS stddev_score
    FROM
      test_scores
  )
SELECT
  t.student,
  t.score,
  (t.score - s.mean_score) / s.stddev_score AS z_score
FROM
  test_scores t,
  stats s
ORDER BY
  t.score;
Expected result
studentscorez_score
Alice10-1
Bob200
Carol301

SQL Server uses STDEV() instead of STDDEV_SAMP(). SQLite requires a CTE-based workaround since it has no built-in standard deviation function.

Useful when

Where this command helps.

  • normalising exam scores from tests with different maximum marks so they can be compared on the same scale
  • flagging individual sensor readings that are more than two standard deviations from the sensor's own mean

Explanation

What the command is doing.

A z-score (standard score) measures how many sample standard deviations a value lies above or below the mean of its group. Normalising to z-scores lets you compare measurements recorded on different scales — for example, combining exam scores from tests with different point ranges. The formula (value − mean) / stddev_samp produces a dimensionless number: 0 means the value equals the mean, +1 means one standard deviation above, −1 means one standard deviation below. In SQL, both the mean and standard deviation can be computed as window aggregates with OVER (), so the result appears alongside each original row without collapsing it. MySQL, MariaDB, and PostgreSQL expose STDDEV_SAMP() as a window function. SQL Server uses STDEV() instead. SQLite has neither, so the standard deviation must be pre-computed in a CTE using the arithmetic formula SQRT((SUM(xx) − SUM(x)²/COUNT()) / (COUNT(*) − 1)) and joined back to the main query. If all values in the group are identical, the standard deviation is zero and the division will produce a division-by-zero error or NULL — guard against this with NULLIF(stddev_samp, 0) in the denominator.

Tags

window-functionsstatisticsnormalizationz-scorestddevanalytics

Related commands

Keep moving through nearby tasks.