SQL select rows having count > certain number

Question

I don't really know how to search for this, probably it's quite easy to do it, but I don't know how to do this. I have a SQL table:

| c1 | c2 | c3 | c4 | c5 |
 data data data data data

So I've 5 columns, and now I want to select the rows with only the following (c1, c2, c3) where that row appears more than 5 times in the table Something like this:

  Select c1, c2, c3 
  From table
  having count(*) > 5 and (all in that count, all rows must have the same values on c1, c2, c3)

Can only do this with basical sql queries. Functions, declarations and etc are not allowed. Don't really know if i'm explaining myself well.

you should write up an example of your dataset and your expected result, that would help a bunch. — Jakumi
– Jakumi, Commented Jul 28, 2016 at 8:48

Raphaël Althaus · Accepted Answer · 2016-07-28 08:39:54Z

2

Not absolutely sure I understand, but my guess would be

select c1, c2, c3
from <yourtable>
group by c1, c2, c3
having count(*) > 5

edited Jul 28, 2016 at 8:39

answered Jul 28, 2016 at 8:31

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Elsendion Over a year ago

No, I don't want the row where all value area equal. I want the distinct count of rows, where the row 1 is equal to row 2.

Raphaël Althaus Over a year ago

@Elsendion or just remove the where clause

Tim Biegeleisen · Accepted Answer · 2016-07-28 08:33:05Z

0

This query will return all records from your original table whose c1, c2, and c3 combined values appear in duplicate more than 5 times. I also included the actual count in the result set.

SELECT t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t2.cardinality
FROM yourTable t1
INNER JOIN
(
    SELECT c1, c2, c3, COUNT(*) AS cardinality
    FROM yourTable
    GROUP BY c1, c2, c3
    HAVING COUNT(*) > 5
) t2
    ON t1.c1 = t2.c1 AND
       t1.c2 = t2.c2 AND
       t1.c3 = t2.c3

answered Jul 28, 2016 at 8:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

nilsman · Accepted Answer · 2016-07-28 08:47:52Z

0

Just treat c1,c2,c3 as a single string c1+c2+c3:

SELECT c1, c2, c3 FROM table WHERE
c1 || c2 || c3 IN (
  SELECT c1 || c2 || c3 FROM table
  GROUP BY c1 || c2 || c3
  HAVING COUNT(*) > 5);

edited Jul 28, 2016 at 8:47

answered Jul 28, 2016 at 8:42

nilsman

3962 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL select rows having count > certain number

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related