CODEWITHSUNDEEP
sqloracle-database
gavenkoa
gavenkoa

Reputation: 48753

SQL query to select all rows where values of field occured N or more times?

Suppose we have table:

create table EVENT("id" number, "date" DATE, "value" number);

I want to get all rows where each selected id occured N or more times. So for:

    id  |    date    | value
   --------------------------
    1   | 2011-01-01 |  100
    1   | 2011-01-02 |  200
    2   | 2011-01-05 |  300
    2   | 2011-03-15 |  800
    3   | 2011-02-01 |  400
    4   | 2011-01-01 |  500
    4   | 2011-04-21 |  600
    4   | 2011-01-01 |  700

and N == 2 I get all rows except id=3, and for N == 3 I get only rows with id=4...

I work with Oracle but seems this type of query require some new knowledge of SQL for me...

Upvotes: 2

Views: 2210

Answers (7)

Martin Smith
Martin Smith

Reputation: 453047

SELECT "id",
       "date",
       "value"
FROM   (SELECT EVENT.*,
               COUNT(*) OVER (PARTITION BY "id") AS CNT
        FROM   EVENT)
WHERE  CNT >= 3

SQL Fiddle

Upvotes: 6

white
white

Reputation: 1913

select 
  e.* 
from 
  event e, 
  (select e1.id, count(*) as id_num from event e1 group by e1.id) as e2
where
  e.id = e2.id
  and e2.id_num >= 3

Upvotes: 1

adrian.riobo
adrian.riobo

Reputation: 495

SELECT *
FROM   EVENT
GROUP  BY "id"
HAVING ( COUNT("id") > N - 1
         AND COUNT("id") < N + 1 );

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

SELECT *
FROM Event
WHERE Id IN (SELECT Id 
             FROM Event
             GROUP BY Id 
             HAVING COUNT(*) > N)

Edit: Martin Smith's answer should have the best performance, the only downside being that you have to list out fields to avoid including the COUNT() in the results.

Upvotes: 2

kcsoft
kcsoft

Reputation: 2947

SELECT * FROM Event
INNER JOIN (
    SELECT id, COUNT(*) AS Cnt FROM Event GROUP BY id
) AS C ON Event.id = C.id
WHERE C.Cnt >= 3

Upvotes: 1

Vishal Suthar
Vishal Suthar

Reputation: 17193

Here you go:

SELECT *
FROM tmp
WHERE id IN (SELECT id FROM tmp GROUP BY id 
             HAVING COUNT(*) > N)

Update value of N as your condition says.

SQL Fiddle DEMO

Upvotes: 1

Andomar
Andomar

Reputation: 238078

select  *
from    Event
where   id in
        (
        select  id
        from    Event
        group by
                id
        having  count(*) > 3 -- For N = 3
        )

Upvotes: 1

Related Questions