SQL group by with diff percentage?

Question

im trying to write an sql query that will group similar items by difference in percentage value. i.e. if there is a difference of less than 2% between 2 or more values, id like them to be grouped together.

e.g.

sampletable
Col Num
-------------
A 201
B 202
C 203
D 204
E 205
F 206

result
A 4
E 2

using something like this:

select min(col), count(*) from sampletable
group by (difference percentage in Num col is less than 2%)


any ideas?

Answer 1

I would look at an inner query to subset the items that you want in your skew, then do a group by on that data set.

Answer 2

SQL is a set of standards for how to query (extract information) from a database. Oracle is a company that makes database software. The Oracle database program is able to use the SQL standards for queries, but it also includes many other features and abilities that are not part of the SQL language. Oracle includes SQL abilities in its software so that the programs can use data from non-Oracle database, since almost all database include the SQL standards as part of their festures. It allows different database softwares to be able to work together. There are program that include "SQL" as part of their name because they are build on the SQL standard. MySQL is probably the best known (and currently administered by Oracle). Microsoft also makes one called SQL Server. But there is not such a software as "SQL". SQL is a set of standards on which database programs are based.

Answer 3

Might be able to do this with a selfjoin, since each row needs to be compared to every other row. Something like



SELECT col1, COUNT(*) - 1 AS "Number within 2%" FROM

(SELECT a.col AS col1, b.col AS col2

FROM someTable a, someTable b

WHERE a.val / b.val BETWEEN 0.98 AND 1.02)

GROUP BY col1



I subtract 1 from the count to exclude the row where a given item is paired with itself (since the ratio will always be 1.0, you'll always have (X,X) even if no other rows qualify)

Answer 4

What result do you expect when the data is



Col Num

-------------

A 206

B 201

C 205

D 202

E 204

F 203



?