Question 114188 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to count the number of occurrences in a self-referencing column?

I am new to SQL and I’ve been struggling with this example for quite a while now.

I have this table:
database table

So, what is asked from me is to produce a count of the number of recommendations each member has made. Order by number of recommendations. The final result should look something like this:
final result

I really am confused, since the values of column recommendedby is actually the id of the member. I don’t know how to "recognize" them as id and not just some values, count how many recommendations each member has and "connect" them to memid column to get to needed result.

So far I managed to do this:

SELECT COUNT(recommendedby)
FROM members
GROUP BY recommendedby

But I’m stuck now. I get a counted number of recommendations for each id, but it’s not connected to actual id. This is my result.

my result

Tags:

3

Answers


  1. 0

    I think this is what you’re looking for:

    select "id"
     , (select count(1) 
          from "members" 
         where "recommendedby" = m."id") 
  from "members" m

    Although using subqueries are not very popular and can cause serious performance issues, this is imho the easiest way to learn what you’re doing.

    Login or Signup to reply.
  2. 0

    You should use a self-join for this:

    SELECT m.id,
       count(r.id) AS recommendations
FROM members AS m
   LEFT JOIN members AS r
      ON r.recommendedby = m.id
GROUP BY m.id
ORDER BY recommendations;

    The left join will make r.id be NULL for members that made no recommendation, and count won’t count such NULL values.

    Login or Signup to reply.
  3. 0

    There are two ways you can go with.

    1. Join + Group By

    This is pretty simple, all you need to do is put the query you made as SubQuery and Join your table with that.

    SELECT members.*, rec.recommendations
FROM members
 LEFT JOIN (
  SELECT COUNT(recommendedby) recommendations, recommendedby
   FROM members
   GROUP BY recommendedby
 ) rec ON rec.recommendedby = members.id
    1. Lateral
    SELECT m1.*, rec.recommendations
FROM members m1,
LATERAL (
 SELECT COUNT(recommendedby) recommendations
   FROM members m2
   WHERE m1.id = m2.id
)

    Hope this can help.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search