How to Find the Missing Number in SQL Column?

Last Updated : 02 Apr, 2023

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.

How to Filter Rows Without Null in a Column in SQL?

vandanakillari54935

Improve

Article Tags :

Practice Tags :

Misc

Finding the maximum value of multiple columns is one of the most common analytical tasks essential for making decisions and analyzing data. Using the MAX() function of SQL, users can find the maximum value in a single column. But to find the maximum value in multiple columns, users need to use other

2 min read

SQL Query to Find the Number of Columns in a Table

SQL stands for a structure query language, which is used in the database to retrieve data, update and modify data in relational databases like MySql, Oracle, etc. And a query is a question or request for data from the database, that is if we ask someone any question then the question is the query. S

4 min read

How to Filter Rows Without Null in a Column in SQL?

Here we will see, how to filter rows without null in a column of an MS SQL Server's database table with the help of a SQL query using IS NOT NULL operator. For the purpose of demonstration, we will be creating a demo_orders table in a database called â€œgeeksâ€œ. Creating the Database: Use the below SQL

2 min read

How to Find Most Frequent Value in Column in SQL?

To find the most frequent value in a column in SQL, use the COUNT() function to get a count of each unique value, sort the result in descending order, and select the first value in the final results set. In SQL, sometimes we need to find frequent values in a column. Finding the Most Frequent Value i

1 min read

How to Get the Type of Columns in SQL

In SQL, the types of columns in a database table play a fundamental role in data management and query execution. Each column is designed to store specific types of data, such as numbers, text, dates, or binary data. Understanding these column types is essential for effective database design, query o

4 min read

SQL Query to Find the Average Value in a Column

In this article, we are going to see how to find the average value in a column in SQL. A column in the SQL table is the vertical catalog structure. In this article, we will be using the Microsoft SQL Server as our database. For the purpose of example, we will be creating a sample table and performin

3 min read

Convert the given numbers into words in Pl/SQL

Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Given a number and task is to convert each digit of the

2 min read

SQL Query to Find the Sum of all Values in a Column

In SQL, calculating the sum of values in a column is a crucial task for performing data analysis and generating reports. The SUM() function helps to calculate the total sum of numeric values from a column, which is especially useful in scenarios like finding total sales, total employees, or total re

5 min read

SQL Query to Get Column Names From a Table

SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do ma

2 min read

How to Check if a Column Exists in a SQL Server Table?

In this article, we will look at how to check if a particular column exists in a database table or not. For checking the existence of a column we need to create the table first. So, let us create a table with some columns and data. Creating table: Syntax: CREATE TABLE table_name ( column1 datatype,

2 min read

How to Find the Missing Number in SQL Column?

Similar Reads