CODEWITHSUNDEEP
sqloracle-database
Lucas Rey
Lucas Rey

Reputation: 467

Query who will output ONLY numbers in field1 who exceeded a specific threshold

I have to make a "simple" query on ORACLE DB who will output ONLY numbers in field1 who exceeded a specific threshold.

In other words, assuming I have the following data in database.

FIELD1               FIELD2
=========            ==============
3291234567           333991123456789
3277654321           333011123456789
3481234567           333101123456789
3291234567           333991123456789
3291234567           333011123456789
3277654321           333015123456789
3277654321           333103123456789
3277654321           333201123456789
3481234567           333112123456789

I want to output only number in field1 with occurrences >= 3, so the query output will be:

FIELD1        FIELD2
=========     ==============
3277654321    333011123456789
3277654321    333015123456789
3277654321    333103123456789
3277654321    333201123456789
3291234567    333991123456789
3291234567    333991123456789
3291234567    333011123456789

Please help!!!! Thanks Lucas

Upvotes: 0

Views: 78

Answers (3)

user2941651
user2941651

Reputation:

UPDATE

Referring to additional information from your comments:

Since subquery in this very case is a set of grouped information serving for identification which FIELD1 values satisfy the condition of having more than 3 occurences in yourtable and because of the fact that this additional info is INNER JOIN-ed to yourtable, it should be enough if you added field filtering with WHERE clause in the top-most query.

So the query with WHERE clause should look like:

     SELECT  A.FIELD1,  A.FIELD2 
        FROM 
        yourtable A 
        INNER JOIN
        (
         SELECT FIELD1, COUNT (1) 
         FROM yourtable
         GROUP BY FIELD1
         HAVING COUNT (1) >= 3
       ) B
       ON A.FIELD1 = B.FIELD1
     WHERE 
       A.FIELD1 = 'some_value'

Again, by your last comment, if you have more fields in yourtable that you need to filter by, you may add them like: 

     ..................
     WHERE 
       A.FIELD1 = 'some_value' 
       AND A.FIELD5 = 'some_field5_val'
       AND (...)

Upvotes: 0

Devon Bessemer
Devon Bessemer

Reputation: 35347

SELECT * FROM db_table WHERE field1 IN 
( SELECT field1 FROM db_table GROUP BY field1 HAVING count(*) >= 3 )

I believe this will work in Oracle and MySQL. Using a subquery, you list all the field1 entries that have three occurrences, then the original query is run to match field1 entries in that list.

Upvotes: 2

David Faber
David Faber

Reputation: 12485

This should work for you (using COUNT(*) as a window function) - I assume you want Oracle even though the question is tagged mysql:

SELECT field1, field2 FROM (
    SELECT field1, field2, COUNT(*) OVER ( PARTITION BY field1 ) field1_cnt
      FROM mytable
) WHERE field1_cnt >= 3;

Accomplishing this in MySQL is a bit trickier since MySQL doesn't have window functions.

Upvotes: 2

Related Questions