How to Find the Missing Number in SQL Column?

Given a column in the table having values from 1 to N, one value will be missed. The task is to find the missing number.

So, let’s start by creating a database first.

Step 1 : Create Database.

Query :

CREATE DATABASE GFG

Step 2 : Use the GFG Database.

Query :

USE GFG

Step 3 : Create a table

Create a table (MissingNumber) to store the data.

Query :

CREATE TABLE MissingNumber( Value INT );

Output :

Step 4 : Insert some data into the database.

Query : 

INSERT INTO MissingNumber
VALUES 
  (1),
  (2), 
  (3), 
  (4),  
  (6), 
  (7), 
  (8);

Output :

Step 5 : SQL query to select the missing number in the column from 1 to N.

Sum of numbers from 1 to N (S)= (N * (N+1)) / 2;

The missing number will be the difference of S and the sum of values in the column.

Query :

SELECT ( (COUNT(Value)+1) * (COUNT(Value)+2) / 2) - SUM(Value) AS Missing FROM MissingNumber

Output :

In the MissingNumber table, 5 is the missing number in the Value column.