Postgresql -how to create condition based on total number of unique rows

Question

My goal is to create a condition where the number of unique rows equals 3 or more,

For an instance - if I got the result:

| id | 
1
2
3

then there are 3 rows return true. (for the existence part I'll use EXISTS)

I've tried to use COUNT(*) and DISTINCT to count all the different unique rows, but as I want to use condition -

using WHERE is impossible due the COUNT function
nor using HAVING - because there's a need to use GROUP BY first, which cause the COUNT(*)=1.

Another requirement - is to use postgresql at version 9.4

My latest attempt which returns nothing because COUNT(*)=1

exists(
     select count(*)
     from (select sid, bno
           from schedule scj inner join revent on scj.eid = revent.reid
           where 12345 = cno AND 'hello' = sid) as foo
     group by sid, bno
     having count(*)>=3)

what result do you want? different rules apply to conditions in different contexts. — Jasen
– Jasen, Commented Jul 29, 2018 at 19:31
The result I was looking for on this question was a boolean value, as my final query is more complex than that, therefore - I was trying to simplify the question. — YanivGK
– YanivGK, Commented Jul 29, 2018 at 19:37

Gordon Linoff · Accepted Answer · 2018-07-29 18:56:42Z

1

You would use:

select (count(*) >= 3)
from result;

result could be a CTE, subquery, or other query logic that generates the rows in your first result set.

So, if this is your query:

select sid, bno
from schedule scj inner join revent on scj.eid = revent.reid
where 12345 = cno AND 'hello' = sid

Then the logic is:

select (count(*) >= 3)
from (select sid, bno
      from schedule scj inner join revent on scj.eid = revent.reid
      where 12345 = cno AND 'hello' = sid
     ) result;

answered Jul 29, 2018 at 18:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

YanivGK Over a year ago

Look like it works, good to know there also conditions on the SELECT section, Thanks!

Collectives™ on Stack Overflow

Postgresql -how to create condition based on total number of unique rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related