Classifying Numbers with SQL

Uncategorized

This puzzle takes us into Discrete Mathematics and Number Theory, where numbers aren’t just values—they’re types. Whether a number is odd, even, prime, or part of a sequence like Fibonacci, Number Theory is all about how we classify them. I started by generating a table of integers and labeling each with its corresponding classification. A simple idea, but it quickly expanded—turns out the number of number types is bigger than I thought. Let’s see what we can uncover.

I’ve provided a brief overview of each classification, along with the relevant SQL, below. However, I encourage you to explore this topic further through your own research to deepen your understanding of it. With that in mind, let’s dive into how we can categorize these classifications.

❗The full Microsoft T-SQL used to create this analysis is located at the end of this post.

Here are the classifications we’ll be working with….

Basic Properties

  • Odd / Even
  • Negative / Positive
  • Integer / Decimal
  • Whole
  • Natural
  • Real

Number Theory

  • Prime
  • Composite
  • Perfect
  • Abundant
  • Deficient

Special Forms

  • Square
  • Triangular
  • Cube
  • Factorial

Sequences

  • Fibonacci

Patterns

  • Palindromic
  • Harshad (Niven)
  • Armstrong (Narcissistic)

Classifications Beyond SQL

Some categories don’t map well to SQL because they involve infinite precision or complex numbers:

  • Irrational numbers (π, √2, e)
  • Rational numbers (fractions in reduced form)
  • Imaginary numbers (involving i = √-1)

Others are possible but too elaborate for this blog post, such as:

  • Happy / Sad Numbers
  • Amicable / Sociable Numbers
  • Highly Composite Numbers
  • Catalan Numbers

Classifying Numbers

Now, let’s dive into how to calculate each classification using SQL.

Creating the Numbers Table

For this analysis, we will use the GENERATE_SERIES to generate a Numbers table for the integers -100 through +100.

SELECT  value AS Number
INTO    ##Numbers
FROM    GENERATE_SERIES(-100, 100);
GO

Even / Odd

Classifying a number as even or odd is one of the simplest number properties.

  • A number is even if it can be divided by 2 with no remainder.
  • A number is odd if it has a remainder of 1 when divided by 2.

Another way to describe even numbers is with the formula 2n. Any integer n multiplied by 2 will always produce an even number. Similarly, numbers of the form 2n + 1 will always be odd.

In SQL, we can use the modulo operator (%) to check for evenness.

-- Even and Odd Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  Number,
        CASE WHEN Number % 2 = 0 THEN 'Even' ELSE 'Odd' END AS NumberType
INTO    ##Odd_Even
FROM    cte_Numbers
ORDER BY 1;
GO

Negative / Positive

Numbers can also be classified based on their sign:

  • Negative numbers are less than zero.
  • Positive numbers are greater than zero.
  • Zero itself is neither positive nor negative, so it is excluded from both categories.

In SQL, we can use a simple CASE expression to classify numbers by comparing them directly to zero.

-- Negative and Positive Numbers
SELECT  Number,
        CASE WHEN Number < 0 THEN 'Negative' WHEN Number > 0 THEN 'Positive' END AS NumberType
INTO    ##Negative_Positive
FROM    ##Numbers
ORDER BY 1;
GO

Integers / Decimals

Another fundamental way to classify numbers is whether they are integers (whole numbers) or decimals (numbers with a fractional part).

  • An integer is any number without a fractional component.
  • A decimal is any number that has digits after the decimal point.

In mathematics, all integers are real numbers, but not all real numbers are integers.

There are two methods in SQL to determine if a number is an integer or a decimal. We will cover them both here.

The first method is to use the FLOOR function.

-- Integers and Decimal Numbers
SELECT  Number,
        CASE WHEN Number = FLOOR(Number) THEN 'Integer' ELSE 'Decimal' END AS NumberType
INTO   ##Integer_Decimal
FROM   ##Numbers;
GO

The second method is to divide by one and determine if a remainder is present.

SELECT  Number,
        CASE WHEN CAST(Number AS DECIMAL(18,10)) % 1 = 0 THEN 'Integer' ELSE 'Decimal' END AS NumberType
--INTO   ##Integer_Decimal
FROM    ##Numbers;
GO

Whole Numbers

Whole numbers are the set of non-negative integers: 0, 1, 2, 3, 4, …

They include zero and all positive counting numbers, but exclude negative numbers and decimals.

-- Whole Numbers
SELECT  Number,
        'Whole' AS NumberType
INTO    ##Whole
FROM    ##Numbers
WHERE   Number = FLOOR(Number) AND
        Number >= 0;
GO

Real Numbers

The real numbers are essentially all the numbers you can plot on a number line. They include:

  • Negative numbers (-3, -2.5, -1)
  • Zero (0)
  • Positive numbers (2, 3.14, 100)
  • Integers and decimals
  • Rational numbers (fractions that can be expressed as decimals)
  • Irrational numbers (π or the square root of 2).

In mathematics, the only things excluded from the real numbers are imaginary numbers.

Since everything we can store in SQL Server (integers, floats, decimals) falls into the category of real numbers, the classification is straightforward: every number in our dataset is real.

-- Real Numbers
SELECT  Number,
        'Real' AS NumberType
INTO    ##Real
FROM    ##Numbers;
GO

Prime Numbers

A prime number is a natural number greater than 1 that has no divisors other than 1 and itself.

Examples:

  • Primes → 2, 3, 5, 7, 11, 13, 17
  • Not Primes → 1 (not prime by definition), 4 (divisible by 2), 9 (divisible by 3), 15 (divisible by 3 and 5)

This makes primes the building blocks of integers, since every number can be uniquely factored into primes (the Fundamental Theorem of Arithmetic).

Here, we will use recursion to determine prime numbers.

-- Prime Numbers
WITH cte_PrimeCheck AS 
(
SELECT  n.Number,
        3 AS Divisor,
        CASE WHEN n.Number > 0 THEN Number ELSE 0 END AS Max_Divisor
FROM    ##Numbers n
WHERE   n.Number > 2 AND
        n.Number % 2 != 0
UNION ALL
SELECT  Number,
        Divisor + 2,
        Max_Divisor
FROM    cte_PrimeCheck
WHERE   Divisor <= Max_Divisor AND
        Number % Divisor != 0
)
SELECT  n.Number,
        CASE 
        WHEN n.Number <= 1 THEN NULL           -- exclude 0 and 1
        WHEN n.Number = 2 THEN 'Prime'         -- special case for 2
        WHEN n.Number % 2 = 0 THEN NULL        -- eliminate even numbers > 2
        WHEN EXISTS (
            SELECT  1
            FROM    cte_PrimeCheck p 
            WHERE   p.number = n.Number AND
                    n.Number % p.divisor = 0
        ) THEN 'Not Prime'                     -- found a divisor
        ELSE 'Prime'                           -- otherwise prime
        END AS NumberType
INTO    ##Prime
FROM    cte_PrimeCheck n
OPTION (MAXRECURSION 0);
GO

Composite Numbers

A composite number is a positive integer greater than 1 that has at least one proper divisor other than 1 and itself. In other words, it’s not prime.

  • Composite: 4 (2×2), 6 (2×3), 8 (2×4), 9 (3×3), 10 (2×5)
  • Not Composite: 1 (neither prime nor composite), 2 (prime), 3 (prime), 5 (prime)

You can determine composites by checking whether a number has any divisor between 2 and n−1.

-- Composite Numbers (Opposite of Prime Numbers)
WITH cte_Numbers AS
(
SELECT Number
FROM   ##Numbers
WHERE  Number = FLOOR(Number)
)
SELECT 
     Number,
    'Composite' AS NumberType
INTO ##Composite
FROM cte_Numbers n
WHERE n.Number > 1
AND EXISTS (
    SELECT 1 
    FROM cte_Numbers d
    WHERE d.Number > 1 
    AND d.Number < n.Number
    AND n.Number % d.Number = 0
);
GO

Perfect Squares

A perfect square is an integer that can be expressed as the square of another integer.

Examples:

  • Perfect Squares: 0 (0²), 1 (1²), 4 (2²), 9 (3²), 16 (4²), 25 (5²)
  • Not Perfect Squares: 2, 3, 5, 6, 7, 8
-- Perfect Squares
SELECT  Number,
        'Square' AS NumberType
INTO    ##Square
FROM    ##Numbers
WHERE   SQRT(Number) = FLOOR(SQRT(Number)) AND
        Number >= 0;
GO

Perfect Cubes

A perfect cube is an integer that can be expressed as another integer raised to the third power.

Examples:

  • Perfect Cubes: −8(-2^3), −1(1^3), 0(0^3), 1 (1^3), 8 (2^3), 27 (3^3), 64 (4^3)
  • Not Perfect Cubes: 2, 3, 5, 6, 7, 9

Unlike squares, cubes can be negative since the cube of a negative integer is also negative.

-- Perfect Cubes
WITH cte_Cube AS
(
SELECT  Number,
        POWER(Number,3.0) AS Cube
FROM    ##Numbers
)
SELECT  Cube AS Number,
       'Cube' AS NumberType
INTO   ##Cube
FROM   cte_Cube
WHERE  Cube IN (SELECT Number FROM ##Numbers)
ORDER BY 1;
GO

Fibonacci Numbers

The Fibonacci sequence is one of the most famous mathematical sequences. Each number in the sequence is the sum of the two numbers before it.

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89…

Fibonacci numbers appear in many natural patterns—spirals in shells, branching in trees, and even the arrangement of sunflower seeds.

-- Fibonacci Numbers
WITH cte_Recursion (PrevNumber, Number) AS
(
SELECT  0, 1
UNION ALL
SELECT  Number,
        PrevNumber + Number
FROM    cte_Recursion
WHERE   Number < 100
)
SELECT  DISTINCT PrevNumber AS Number,
        'Fibonacci' AS NumberType
INTO    ##Fibonacci
FROM    cte_Recursion
OPTION  (MAXRECURSION 0);
GO

Triangular Numbers

A triangular number is a number that can form an equilateral triangular dot pattern.

Examples:

  • Triangular: 1, 3, 6, 10, 15, 21, 28, …
  • Not Triangular: 2, 4, 5, 7, 8, 9

For instance, 6 is triangular because: 6 = 1+2+3

Visually, you could arrange six dots into a perfect triangle.

We can use the formula n(n+1)/2 to generate triangular numbers and check if a candidate number matches:

-- Triangular Numbers
SELECT  Number,
        'Triangular' AS NumberType
INTO    ##Triangular
FROM    ##Numbers
WHERE   Number > 0
AND EXISTS (
    SELECT 1
    FROM ##Numbers n
    WHERE n.Number > 0
    AND n.Number * (n.Number + 1) / 2 = ##Numbers.Number
);
GO

Palindromic Numbers

A palindromic number is a number that reads the same forwards and backwards, much like the word racecar.

Examples:

  • Palindromic: 0, 1, 22, 121, 1331, 12321
  • Not Palindromic: 12, 123, 4567

By convention, we normally exclude negative numbers, since the minus sign (-) breaks the symmetry. For example, -121 reversed becomes 121-, which is not the same.

-- Palindromic Numbers
SELECT  Number,
        'Palindromic' AS NumberType
INTO    ##Palindromic
FROM    ##Numbers
WHERE   CAST(Number AS VARCHAR(20)) = REVERSE(CAST(Number AS VARCHAR(20))) AND
        Number >= 0;
GO

Abundant Numbers

An abundant number is a positive integer whose sum of proper divisors (all positive divisors less than the number itself) is greater than the number.

Examples:

  • Abundant: 12 (divisors: 1, 2, 3, 4, 6 → sum = 16 > 12), 18 (1, 2, 3, 6, 9 → 21 > 18), 20 (1, 2, 4, 5, 10 → 22 > 20)
  • Not Abundant: 6 (perfect), 8 (deficient), 28 (perfect)

Abundant numbers sit alongside deficient (sum < n) and perfect (sum = n) numbers; together they partition the positive integers >1.

--Abundant Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  n.Number,
        'Abundant' AS NumberType
INTO    ##Abundant
FROM    cte_Numbers n
WHERE   n.Number > 1
AND (
    SELECT  SUM(d.Number)
    FROM    cte_Numbers d
    WHERE   d.Number > 0 
      AND   d.Number < n.Number
      AND   n.Number % d.Number = 0
) > n.Number;
GO

Deficient Numbers

A deficient number is a positive integer whose sum of proper divisors (all positive divisors less than the number itself) is less than the number.

Examples:

  • Deficient: 8 (divisors: 1, 2, 4 → sum = 7<8), 10 (1, 2, 5 → 8<10), 14 (1, 2, 7 → 10<14)
  • Not Deficient: 6 (perfect), 12 (abundant), 28 (perfect)

Deficient, perfect, and abundant numbers form a clean trio that partitions the integers greater than 1.

-- Deficient Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  n.Number,
        'Deficient' AS NumberType
INTO    ##Deficient
FROM    cte_Numbers n
WHERE   n.Number > 1
AND (
    SELECT  SUM(d.Number)
    FROM    cte_Numbers d
    WHERE   d.Number > 0 
      AND   d.Number < n.Number
      AND   n.Number % d.Number = 0
) < n.Number;
GO

Perfect Numbers

A perfect number is a positive integer that equals the sum of its proper divisors (all positive divisors less than the number itself).

Classic examples:

  • 6 → divisors: 1, 2, 3 → 1+2+3 = 6
  • Next few: 496, 8128 (they get rare quickly!)
  • 28 → divisors: 1, 2, 4, 7, 14 → 1+2+4+7+14 = 28

Convention note: 1 is not perfect (its proper-divisor sum is 0).

-- Perfect Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  n.Number,
        'Perfect' AS NumberType
INTO    ##Perfect
FROM    cte_Numbers n
WHERE n.Number > 1
AND (
    SELECT  SUM(d.Number)
    FROM    cte_Numbers d
    WHERE   d.Number > 0 
      AND   d.Number < n.Number
      AND   n.Number % d.Number = 0
) = n.Number;
GO

Harshad (Niven) Numbers

A Harshad (or Niven) number is a positive integer that is divisible by the sum of its digits.

Examples:

  • 18 → digits 1+8 = 9; 18 mod  9 = 0 → Harshad
  • 21 → digits 2+1 = 3; 21 mod 3 = 0 → Harshad
  • 19 → digits 1+9 = 10; 19 mod 10 = 9 → not Harshad
-- Harshad (Niven) Numbers
WITH cte_Digits AS
(
SELECT  n.Number,
        CAST(SUBSTRING(s.Digits, v.n, 1) AS INT) AS Digit
FROM    ##Numbers n
        CROSS APPLY (SELECT CAST(ABS(n.Number) AS VARCHAR(20)) AS Digits) s
        CROSS APPLY (SELECT TOP (LEN(s.Digits)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.objects) v
), 
cte_DigitSums AS
(
SELECT  Number,
        SUM(Digit) AS DigitSum
FROM    cte_Digits
GROUP BY Number
)
SELECT  ds.Number,
        'Harshad (Niven)' AS NumberType
INTO    ##Harshad
FROM    cte_DigitSums ds
WHERE   ds.Number > 0  AND
        ds.Number % ds.DigitSum = 0
ORDER BY ds.Number;
GO

Armstrong (Narcissistic) Numbers

An Armstrong (or narcissistic) number is an integer that is equal to the sum of its digits, each raised to the power of the number of digits.

Examples:

  • 153 → 1^3 + 5^3 + 3^3 = 153 → Armstrong
  • 370 → 3^3 + 7^3 + 0^3 = 370 → Armstrong
  • 9474 → 9^4+4^4+7^4+4^4 = 9474 → Armstrong
  • 123 → 1^3+2^3+3^3 = 36 ≠ 123 → not Armstrong

Typically, we consider non-negative integers for this classification.

-- Armstrong (Narcissistic) Numbers
WITH cte_Digits AS
(
SELECT  n.Number,
       LEN(CAST(ABS(n.Number) AS VARCHAR(20))) AS NumDigits,
       CAST(SUBSTRING(s.Digits, v.n, 1) AS INT) AS Digit,
       s.Digits,
       v.n
FROM   ##Numbers n CROSS APPLY 
       (SELECT CAST(ABS(n.Number) AS VARCHAR(20)) AS Digits) s CROSS APPLY 
       (SELECT TOP (LEN(s.Digits)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.objects) v
),
cte_DigitPowers AS
(
SELECT  Number,
        SUM(POWER(Digit, NumDigits)) AS DigitPowerSum
FROM    cte_Digits
GROUP BY Number
)
SELECT  dp.Number,
        'Armstrong (Narcissistic)' AS NumberType
INTO    ##Armstrong
FROM    cte_DigitPowers dp
WHERE   dp.Number = dp.DigitPowerSum AND
        dp.Number >= 0
ORDER BY dp.Number;
GO

Factorial Numbers

A factorial number is any number that equals n! for some non-negative integer n.

Examples:

  • 0! = 1
  • 1! = 1
  • 2! = 2
  • 3! = 6
  • 4! = 24
  • 5! = 120 ….
-- Factorial Numbers
WITH cte_Bound AS
(
SELECT  CAST(ABS(MAX(CAST(Number AS BIGINT))) AS BIGINT) AS MaxAbs
FROM    ##Numbers
),
cte_Factorials AS
(
SELECT  0 AS n, 
        CAST(1 AS DECIMAL(38,0)) AS Fact
FROM    cte_Bound
UNION ALL
SELECT  n + 1,
        CAST(Fact * (n + 1) AS DECIMAL(38,0))
FROM    cte_Factorials CROSS JOIN 
        cte_Bound
WHERE   CAST(Fact * (n + 1) AS DECIMAL(38,0)) <= MaxAbs
)
SELECT  DISTINCT
        CAST(f.Fact AS BIGINT) AS Number,
        'Factorial' AS NumberType
INTO    ##Factorial
FROM    cte_Factorials f INNER JOIN   
        (
        SELECT DISTINCT CAST(Number AS DECIMAL(38,0)) AS NumberInt
        FROM ##Numbers
        WHERE Number >= 0 AND Number = FLOOR(Number)
       ) n
       ON n.NumberInt = f.Fact
ORDER BY Number
OPTION (MAXRECURSION 32767);
GO

Summary by Group

Now that each number has been individually classified, we can also group them together by their classification profile. This allows us to identify which numbers share the same combination of properties.

For example:

  • All negative even integers collapse into a single group.
  • All abundant even numbers under 100 are grouped together.
  • Certain “special” numbers (like 0, 1, 28, etc.) remain in groups of their own.

Count = 1 (29 groups)

  • Number: 0
    Classifications: Even, Integer, Whole Number, Real, Square, Cube, Fibonacci, Palindromic
  • Number: 1
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Square, Cube, Fibonacci, Triangular, Palindromic
  • Number: 2
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Prime, Fibonacci, Palindromic, Deficient
  • Number: 3
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Fibonacci, Triangular, Palindromic, Deficient
  • Number: 4
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Square, Palindromic, Deficient
  • Number: 5
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Prime, Fibonacci, Palindromic, Deficient
  • Number: 6
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Triangular, Palindromic, Perfect
  • Number: 7
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Prime, Palindromic, Deficient
  • Number: 8
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Cube, Fibonacci, Palindromic, Deficient
  • Number: 9
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Square, Palindromic, Deficient
  • Number: 10
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Triangular, Deficient
  • Number: 11
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Prime, Palindromic, Deficient
  • Number: 16
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Square, Deficient
  • Number: 21
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Fibonacci, Triangular, Deficient
  • Number: 24
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Abundant
  • Number: 27
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Cube, Deficient
  • Number: 28
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Triangular, Perfect
  • Number: 34
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Fibonacci, Deficient
  • Number: 36
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Square, Triangular, Abundant
  • Number: 45
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Triangular, Deficient
  • Number: 50
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Deficient
  • Number: 55
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Fibonacci, Triangular, Palindromic, Deficient
  • Number: 63
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Deficient
  • Number: 64
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Square, Cube, Deficient
  • Number: 66
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Triangular, Palindromic, Abundant
  • Number: 78
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Triangular, Abundant
  • Number: 81
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Square, Deficient
  • Number: 88
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Palindromic, Abundant
  • Number: 100
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Square, Abundant

Count = 2 (7 groups)

  • Numbers: -1, -27
    Classifications: Odd, Negative, Integer, Real, Cube
  • Numbers: -8, -64
    Classifications: Even, Negative, Integer, Real, Cube
  • Numbers: 44, 22
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Palindromic, Deficient
  • Numbers: 49, 25
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Square, Deficient
  • Numbers: 89, 13
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Prime, Fibonacci, Deficient
  • Numbers: 91, 15
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Triangular, Deficient
  • Numbers: 96, 56
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Abundant

Count = 3 (1 group)

  • Numbers: 99, 77, 33
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Palindromic, Deficient

Count = 11 (1 group)

  • Numbers: 95, 93, 87, 85, 75, 69, 65, 57, 51, 39, 35
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Not Prime, Composite, Deficient

Count = 14 (1 group)

  • Numbers: 90, 84, 80, 72, 70, 60, 54, 48, 42, 40, 30, 20, 18, 12
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Abundant

Count = 16 (1 group)

  • Numbers: 98, 94, 92, 86, 82, 76, 74, 68, 62, 58, 52, 46, 38, 32, 26, 14
    Classifications: Even, Positive, Integer, Whole Number, Natural Number, Real, Composite, Deficient

Count = 18 (1 group)

  • Numbers: 97, 83, 79, 73, 71, 67, 61, 59, 53, 47, 43, 41, 37, 31, 29, 23, 19, 17
    Classifications: Odd, Positive, Integer, Whole Number, Natural Number, Real, Prime, Deficient

Count = 48 (2 groups)

  • Numbers: -2,-4,-6,-10,-12,-14,-16,-18,-20,-22,-24,-26,-28,-30,-32,-34,-36,-38,-40,-42,-44,-46,-48,-50,-52,-54,-56,-58,-60,-62,-66,-68,-70,-72,-74,-76,-78,-80,-82,-84,-86,-88,-90,-92,-94,-96,-98,-100
    Classifications: Even, Negative, Integer, Real
  • Numbers: -3,-5,-7,-9,-11,-13,-15,-17,-19,-21,-23,-25,-29,-31,-33,-35,-37,-39,-41,-43,-45,-47,-49,-51,-53,-55,-57,-59,-61,-63,-65,-67,-69,-71,-73,-75,-77,-79,-81,-83,-85,-87,-89,-91,-93,-95,-97,-99
    Classifications: Odd, Negative, Integer, Real

To create a table of the above classifications, you can use the following SQL.

-- Results
SELECT
    STRING_AGG(CONVERT(varchar(50), n.Number), ',') WITHIN GROUP (ORDER BY n.Number DESC) AS numbers,
    COUNT(*) AS count,
    oe.NumberType  AS Even_Odd,
    np.NumberType  AS Negative_Positive,
    id.NumberType  AS Integer_Decimal,
    w.NumberType   AS Whole,
    nat.NumberType AS Natural,
    r.NumberType   AS Real,
    p.NumberType   AS Prime,
    c.NumberType   AS Composite,
    sq.NumberType  AS Square,
    cu.NumberType  AS Cube,
    fib.NumberType AS Fibonacci,
    tri.NumberType AS Triangular,
    pal.NumberType AS Palindromic,
    ab.NumberType  AS Abundant,
    de.NumberType  AS Deficient,
    pe.NumberType  AS Perfect,
    h.NumberType   AS Harshad,
    a.NumberType   AS Armstrong,
    f.NumberType   AS Factorial
INTO ##Result
FROM   ##Numbers n
LEFT JOIN ##Odd_Even            oe  ON n.Number = oe.Number
LEFT JOIN ##Negative_Positive   np  ON n.Number = np.Number
LEFT JOIN ##Integer_Decimal     id  ON n.Number = id.Number
LEFT JOIN ##Whole               w   ON n.Number = w.Number
LEFT JOIN ##Natural             nat ON n.Number = nat.Number
LEFT JOIN ##Real                r   ON n.Number = r.Number
LEFT JOIN ##Prime               p   ON n.Number = p.Number
LEFT JOIN ##Composite           c   ON n.Number = c.Number
LEFT JOIN ##Square              sq  ON n.Number = sq.Number
LEFT JOIN ##Cube                cu  ON n.Number = cu.Number
LEFT JOIN ##Fibonacci           fib ON n.Number = fib.Number
LEFT JOIN ##Triangular          tri ON n.Number = tri.Number
LEFT JOIN ##Palindromic         pal ON n.Number = pal.Number
LEFT JOIN ##Abundant            ab  ON n.Number = ab.Number
LEFT JOIN ##Deficient           de  ON n.Number = de.Number
LEFT JOIN ##Perfect             pe  ON n.Number = pe.Number
LEFT JOIN ##Harshad             h   ON n.Number = h.Number
LEFT JOIN ##Armstrong           a   ON n.Number = a.Number
LEFT JOIN ##Factorial           f   ON n.Number = f.Number
GROUP BY 
oe.NumberType,
np.NumberType,
id.NumberType,
w.NumberType,
nat.NumberType,
r.NumberType,
p.NumberType,
c.NumberType,
sq.NumberType,
cu.NumberType,
fib.NumberType,
tri.NumberType,
pal.NumberType,
ab.NumberType,
de.NumberType,
pe.NumberType,
h.NumberType,
a.NumberType,
f.NumberType
ORDER BY 2,1;
GO

If you wish to execute the analysis in full. Here is the full SQL.

DROP TABLE IF EXISTS ##Numbers;
DROP TABLE IF EXISTS ##Odd_Even;
DROP TABLE IF EXISTS ##Negative_Positive;
DROP TABLE IF EXISTS ##Integer_Decimal;
DROP TABLE IF EXISTS ##Whole;
DROP TABLE IF EXISTS ##Natural;
DROP TABLE IF EXISTS ##Real;
DROP TABLE IF EXISTS ##Prime;
DROP TABLE IF EXISTS ##Composite;
DROP TABLE IF EXISTS ##Square;
DROP TABLE IF EXISTS ##Cube;
DROP TABLE IF EXISTS ##Fibonacci;
DROP TABLE IF EXISTS ##Triangular;
DROP TABLE IF EXISTS ##Palindromic;
DROP TABLE IF EXISTS ##Abundant;
DROP TABLE IF EXISTS ##Deficient;
DROP TABLE IF EXISTS ##Perfect;
DROP TABLE IF EXISTS ##Harshad
DROP TABLE IF EXISTS ##Armstrong
DROP TABLE IF EXISTS ##Factorial
DROP TABLE IF EXISTS ##Result;
GO
SET NOCOUNT ON;
GO
DROP TABLE IF EXISTS ##Numbers;
GO
SELECT  value AS Number
INTO    ##Numbers
FROM    GENERATE_SERIES(-100, 100);
GO
--------------------------------------------
--------------------------------------------
-- Even and Odd Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  Number,
        CASE WHEN Number % 2 = 0 THEN 'Even' ELSE 'Odd' END AS NumberType
INTO    ##Odd_Even
FROM    cte_Numbers
ORDER BY 1;
GO
--------------------------------------------
--------------------------------------------
-- Negative and Positive Numbers
SELECT  Number,
        CASE WHEN Number < 0 THEN 'Negative' WHEN Number > 0 THEN 'Positive' END AS NumberType
INTO    ##Negative_Positive
FROM    ##Numbers
ORDER BY 1;
GO
--------------------------------------------
--------------------------------------------
-- Integers and Decimal Numbers
SELECT  Number,
        CASE WHEN Number = FLOOR(Number) THEN 'Integer' ELSE 'Decimal' END AS NumberType
INTO   ##Integer_Decimal
FROM   ##Numbers;
GO
SELECT  Number,
        CASE WHEN CAST(Number AS DECIMAL(18,10)) % 1 = 0 THEN 'Integer' ELSE 'Decimal' END AS NumberType
--INTO   ##Integer_Decimal
FROM    ##Numbers;
GO
--------------------------------------------
--------------------------------------------
-- Whole Numbers
SELECT  Number,
        'Whole' AS NumberType
INTO    ##Whole
FROM    ##Numbers
WHERE   Number = FLOOR(Number) AND
        Number >= 0;
GO
--------------------------------------------
--------------------------------------------
-- Real Numbers
SELECT  Number,
        'Real' AS NumberType
INTO    ##Real
FROM    ##Numbers;
GO
--------------------------------------------
--------------------------------------------
-- Prime Numbers
WITH cte_PrimeCheck AS 
(
SELECT  n.Number,
        3 AS Divisor,
        CASE WHEN n.Number > 0 THEN Number ELSE 0 END AS Max_Divisor
FROM    ##Numbers n
WHERE   n.Number > 2 AND
        n.Number % 2 != 0
UNION ALL
SELECT  Number,
        Divisor + 2,
        Max_Divisor
FROM    cte_PrimeCheck
WHERE   Divisor <= Max_Divisor AND
        Number % Divisor != 0
)
SELECT  n.Number,
        CASE 
        WHEN n.Number <= 1 THEN NULL           -- exclude 0 and 1
        WHEN n.Number = 2 THEN 'Prime'         -- special case for 2
        WHEN n.Number % 2 = 0 THEN NULL        -- eliminate even numbers > 2
        WHEN EXISTS (
            SELECT  1
            FROM    cte_PrimeCheck p 
            WHERE   p.number = n.Number AND
                    n.Number % p.divisor = 0
        ) THEN 'Not Prime'                     -- found a divisor
        ELSE 'Prime'                           -- otherwise prime
        END AS NumberType
INTO    ##Prime
FROM    cte_PrimeCheck n
OPTION (MAXRECURSION 0);
GO
--------------------------------------------
--------------------------------------------
-- Natural Numbers (also called Counting Numbers)
SELECT  Number,
        'Natural' AS NumberType
INTO    ##Natural
FROM    ##Numbers
WHERE   Number = FLOOR(Number) AND
        Number >= 1;
GO
--------------------------------------------
--------------------------------------------
-- Composite Numbers (Opposite of Prime Numbers)
WITH cte_Numbers AS
(
SELECT Number
FROM   ##Numbers
WHERE  Number = FLOOR(Number)
)
SELECT 
     Number,
    'Composite' AS NumberType
INTO ##Composite
FROM cte_Numbers n
WHERE n.Number > 1
AND EXISTS (
    SELECT 1 
    FROM cte_Numbers d
    WHERE d.Number > 1 
    AND d.Number < n.Number
    AND n.Number % d.Number = 0
);
GO
--------------------------------------------
--------------------------------------------
-- Perfect Squares
SELECT  Number,
        'Square' AS NumberType
INTO    ##Square
FROM    ##Numbers
WHERE   SQRT(Number) = FLOOR(SQRT(Number)) AND
        Number >= 0;
GO
--------------------------------------------
--------------------------------------------
-- Perfect Cubes
WITH cte_Cube AS
(
SELECT  Number,
        POWER(Number,3.0) AS Cube
FROM    ##Numbers
)
SELECT  Cube AS Number,
       'Cube' AS NumberType
INTO   ##Cube
FROM   cte_Cube
WHERE  Cube IN (SELECT Number FROM ##Numbers)
ORDER BY 1;
GO
--------------------------------------------
--------------------------------------------
-- Fibonacci Numbers
WITH cte_Recursion (PrevNumber, Number) AS
(
SELECT  0, 1
UNION ALL
SELECT  Number,
        PrevNumber + Number
FROM    cte_Recursion
WHERE   Number < 100
)
SELECT  DISTINCT PrevNumber AS Number,
        'Fibonacci' AS NumberType
INTO    ##Fibonacci
FROM    cte_Recursion
OPTION  (MAXRECURSION 0);
GO
--------------------------------------------
--------------------------------------------
-- Triangular Numbers
-- Find triangular numbers using formula: n(n+1)/2
SELECT  Number,
        'Triangular' AS NumberType
INTO    ##Triangular
FROM    ##Numbers
WHERE   Number > 0
AND EXISTS (
    SELECT 1
    FROM ##Numbers n
    WHERE n.Number > 0
    AND n.Number * (n.Number + 1) / 2 = ##Numbers.Number
);
GO
--------------------------------------------
--------------------------------------------
-- Palindromic Numbers
SELECT  Number,
        'Palindromic' AS NumberType
INTO    ##Palindromic
FROM    ##Numbers
WHERE   CAST(Number AS VARCHAR(20)) = REVERSE(CAST(Number AS VARCHAR(20))) AND
        Number >= 0;
GO
--------------------------------------------
--------------------------------------------
--Abundant Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  n.Number,
        'Abundant' AS NumberType
INTO    ##Abundant
FROM    cte_Numbers n
WHERE   n.Number > 1
AND (
    SELECT  SUM(d.Number)
    FROM    cte_Numbers d
    WHERE   d.Number > 0 
      AND   d.Number < n.Number
      AND   n.Number % d.Number = 0
) > n.Number;
GO
--------------------------------------------
--------------------------------------------
-- Deficient Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  n.Number,
        'Deficient' AS NumberType
INTO    ##Deficient
FROM    cte_Numbers n
WHERE   n.Number > 1
AND (
    SELECT  SUM(d.Number)
    FROM    cte_Numbers d
    WHERE   d.Number > 0 
      AND   d.Number < n.Number
      AND   n.Number % d.Number = 0
) < n.Number;
GO
--------------------------------------------
--------------------------------------------
-- Perfect Numbers
WITH cte_Numbers AS
(
SELECT  Number
FROM    ##Numbers
WHERE   Number = FLOOR(Number)
)
SELECT  n.Number,
        'Perfect' AS NumberType
INTO    ##Perfect
FROM    cte_Numbers n
WHERE n.Number > 1
AND (
    SELECT  SUM(d.Number)
    FROM    cte_Numbers d
    WHERE   d.Number > 0 
      AND   d.Number < n.Number
      AND   n.Number % d.Number = 0
) = n.Number;
GO
--------------------------------------------
--------------------------------------------
-- Harshad (Niven) Numbers
WITH cte_Digits AS
(
SELECT  n.Number,
        CAST(SUBSTRING(s.Digits, v.n, 1) AS INT) AS Digit
FROM    ##Numbers n
        CROSS APPLY (SELECT CAST(ABS(n.Number) AS VARCHAR(20)) AS Digits) s
        CROSS APPLY (SELECT TOP (LEN(s.Digits)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.objects) v
), 
cte_DigitSums AS
(
SELECT  Number,
        SUM(Digit) AS DigitSum
FROM    cte_Digits
GROUP BY Number
)
SELECT  ds.Number,
        'Harshad (Niven)' AS NumberType
INTO    ##Harshad
FROM    cte_DigitSums ds
WHERE   ds.Number > 0  AND
        ds.Number % ds.DigitSum = 0
ORDER BY ds.Number;
GO
--------------------------------------------
--------------------------------------------
-- Armstrong (Narcissistic) Numbers
WITH cte_Digits AS
(
SELECT  n.Number,
       LEN(CAST(ABS(n.Number) AS VARCHAR(20))) AS NumDigits,
       CAST(SUBSTRING(s.Digits, v.n, 1) AS INT) AS Digit,
       s.Digits,
       v.n
FROM   ##Numbers n CROSS APPLY 
       (SELECT CAST(ABS(n.Number) AS VARCHAR(20)) AS Digits) s CROSS APPLY 
       (SELECT TOP (LEN(s.Digits)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.objects) v
),
cte_DigitPowers AS
(
SELECT  Number,
        SUM(POWER(Digit, NumDigits)) AS DigitPowerSum
FROM    cte_Digits
GROUP BY Number
)
SELECT  dp.Number,
        'Armstrong (Narcissistic)' AS NumberType
INTO    ##Armstrong
FROM    cte_DigitPowers dp
WHERE   dp.Number = dp.DigitPowerSum AND
        dp.Number >= 0
ORDER BY dp.Number;
GO
--------------------------------------------
--------------------------------------------
-- Factorial Numbers
WITH cte_Bound AS
(
SELECT  CAST(ABS(MAX(CAST(Number AS BIGINT))) AS BIGINT) AS MaxAbs
FROM    ##Numbers
),
cte_Factorials AS
(
SELECT  0 AS n, 
        CAST(1 AS DECIMAL(38,0)) AS Fact
FROM    cte_Bound
UNION ALL
SELECT  n + 1,
        CAST(Fact * (n + 1) AS DECIMAL(38,0))
FROM    cte_Factorials CROSS JOIN 
        cte_Bound
WHERE   CAST(Fact * (n + 1) AS DECIMAL(38,0)) <= MaxAbs
)
SELECT  DISTINCT
        CAST(f.Fact AS BIGINT) AS Number,
        'Factorial' AS NumberType
INTO    ##Factorial
FROM    cte_Factorials f INNER JOIN   
        (
        SELECT DISTINCT CAST(Number AS DECIMAL(38,0)) AS NumberInt
        FROM ##Numbers
        WHERE Number >= 0 AND Number = FLOOR(Number)
       ) n
       ON n.NumberInt = f.Fact
ORDER BY Number
OPTION (MAXRECURSION 32767);
GO
--------------------------------------------
--------------------------------------------
-- Results
SELECT
    STRING_AGG(CONVERT(varchar(50), n.Number), ',') WITHIN GROUP (ORDER BY n.Number DESC) AS numbers,
    COUNT(*) AS count,
    oe.NumberType  AS Even_Odd,
    np.NumberType  AS Negative_Positive,
    id.NumberType  AS Integer_Decimal,
    w.NumberType   AS Whole,
    nat.NumberType AS Natural,
    r.NumberType   AS Real,
    p.NumberType   AS Prime,
    c.NumberType   AS Composite,
    sq.NumberType  AS Square,
    cu.NumberType  AS Cube,
    fib.NumberType AS Fibonacci,
    tri.NumberType AS Triangular,
    pal.NumberType AS Palindromic,
    ab.NumberType  AS Abundant,
    de.NumberType  AS Deficient,
    pe.NumberType  AS Perfect,
    h.NumberType   AS Harshad,
    a.NumberType   AS Armstrong,
    f.NumberType   AS Factorial
INTO ##Result
FROM   ##Numbers n
LEFT JOIN ##Odd_Even            oe  ON n.Number = oe.Number
LEFT JOIN ##Negative_Positive   np  ON n.Number = np.Number
LEFT JOIN ##Integer_Decimal     id  ON n.Number = id.Number
LEFT JOIN ##Whole               w   ON n.Number = w.Number
LEFT JOIN ##Natural             nat ON n.Number = nat.Number
LEFT JOIN ##Real                r   ON n.Number = r.Number
LEFT JOIN ##Prime               p   ON n.Number = p.Number
LEFT JOIN ##Composite           c   ON n.Number = c.Number
LEFT JOIN ##Square              sq  ON n.Number = sq.Number
LEFT JOIN ##Cube                cu  ON n.Number = cu.Number
LEFT JOIN ##Fibonacci           fib ON n.Number = fib.Number
LEFT JOIN ##Triangular          tri ON n.Number = tri.Number
LEFT JOIN ##Palindromic         pal ON n.Number = pal.Number
LEFT JOIN ##Abundant            ab  ON n.Number = ab.Number
LEFT JOIN ##Deficient           de  ON n.Number = de.Number
LEFT JOIN ##Perfect             pe  ON n.Number = pe.Number
LEFT JOIN ##Harshad             h   ON n.Number = h.Number
LEFT JOIN ##Armstrong           a   ON n.Number = a.Number
LEFT JOIN ##Factorial           f   ON n.Number = f.Number
GROUP BY 
oe.NumberType,
np.NumberType,
id.NumberType,
w.NumberType,
nat.NumberType,
r.NumberType,
p.NumberType,
c.NumberType,
sq.NumberType,
cu.NumberType,
fib.NumberType,
tri.NumberType,
pal.NumberType,
ab.NumberType,
de.NumberType,
pe.NumberType,
h.NumberType,
a.NumberType,
f.NumberType
ORDER BY 2,1;
GO
--Concat
SELECT  SUM(count) AS [Count],
        Numbers,
        CONCAT_WS(', ',Even_Odd,Negative_Positive,Integer_Decimal,Whole,Natural,[Real],Prime,Composite,[Square],[Cube],Fibonacci,Triangular,Palindromic,Abundant,Deficient,Perfect)
FROM    ##Result
GROUP BY Numbers,
        CONCAT_WS(', ',Even_Odd,Negative_Positive,Integer_Decimal,Whole,Natural,[Real],Prime,Composite,[Square],[Cube],Fibonacci,Triangular,Palindromic,Abundant,Deficient,Perfect)
ORDER BY 1,2;
GO

Happy coding!

Published

Leave a Reply