CODEWITHSUNDEEP
sqlsql-serverduplicates
FSchneider91
FSchneider91

Reputation: 1

SQL: identify if there are multiples (not duplicates) in a column

I am currently struggling in identifying a possibility to identify certain patterns in my data using SSMS. I wish to identify rows that contain multiples (x2, x3, or x*4) of an entry within the same column.

I really have no clue on how to even start my "where" statement right now.

SELECT  [numbers], [product_ID]
FROM [db].[dbo].[tablename]
WHERE [numbers] = numbers*2

My problem is that with the code above I can obviously only identify zeros.

Google only helps me out with finding duplicates but I can't find a way to identify multiples of a value...

My desired result would be a table that only contains numbers (linked to product_IDs) that are multiples of each other

Anyone can help me out here?

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269813

If a column contains multiples, then all are multiples of the smallest non-zero value. Let me assume the values are positive or zero for this purpose.

So, you can determine if this is the case using window functions and modulo arithmetic:

select t.*
from (select t.*,
             min(case when number > 0 then number end) over () as min_number
      from t
     ) t
where number % min_number = 0 or min_number = 1;

If you want to know if all numbers meet this criteria, use aggregation:

select (case when min(number % min_number) = 0 then 'all multiples' else 'oops' end)
from (select t.*,
             min(case when number > 0 then number end) over () as min_number
      from t
     ) t

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89090

My desired result would be a table that only contains numbers (linked to product_IDs) that are multiples of each other

You'll need to test all pairs of rows, which means a CROSS JOIN.

Something like this:

with q as
(
SELECT  [numbers], 
        [product_ID], 
        cast(a.numbers as float) / coalesce(b.numbers, null) ratio
FROM [tablename] a
CROSS JOIN [tablename] b
)
select *
from q
where ratio = cast(ratio as bigint)
and ratio > 1

Upvotes: 0

Related Questions