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

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:

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

CodePudding user response：

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.

CodePudding user response：

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.