Having Count isnt working

Question

Not sure what I did this morning but I must have hit my head really hard

Here is my query

select c.id as cid,c.studentid as cstudentid              
from certification c
group by c.id,c.studentid
order by c.studentid

My results are this

 cid studentid
 35267  6400
 5332   6401
 35271  6402
144024 6402
252727 6402
434317 6402
529734 6405
...

I want the student ids that are repeared more than twice like 6402

I tried the basic query like this

select c.id as cid,c.studentid as cstudentid              
from certification c
group by c.id,c.studentid
having count(c.studentid) > 2
order by c.studentid

the output is Empty/nothing/no table...

System: Postgres 9.3

What am I doing wrong?

Answer 1

Your query doesn't do your expectations. It returns all rows with count of One on each row. Because you've grouped both of the columns. Also it can't find the related rows to aggregate. So you could change your query to group it by one column (c.studentId) and aggregate on another (c.id). It could be like this following code:

select count(c.id), c.studentid as cstudentid              
from certification c
group by c.studentid
having count(c.id) > 2
order by c.studentid

If you need the duplicated c.id's you should write a CTE query. The second answer of this post will help you.

Answer 2

Try this: But, you should specify with the output: Remove cid from group by as, from your table it seemed that cid is unique

select c.studentid as cstudentid              
from certification c
group by c.studentid
having count(c.studentid) > 2
order by c.studentid