Good day! There is such a structure:

peopleid | Name | Father | Mother.
-----------------------------------
1 | Vasia | NULL | NULL
2 | Sveta | 4 | 3.
3 | Lena | NULL | NULL
4 | Andrey | NULL | NULL
5 | Kostik | 1 | NULL
6 | Olga | 4 | 3.

i.e. Andrei 2 Child Light and Olga, Vasi 1 – Kostik.

I need to withdraw your father from whom the most children.
I learned to find my father’s ID, which is most often found in the Father column.
But you need to still withdraw the name that corresponds to this Father. Those. Somehow to do this, approximately, another conclusion with the condition “Name from Family Where Peopleid = Count (Father)”

Select Father, Count (Father) as value_occurrence
  From Family.
    Where Father Is Not Null
  GROUP by Father.
  Order by Value_Oscurrence Desc
  Limit 1;

Answer 1, Authority 100%

The simplest is a subquery or connection. The option with the connection was already given in the comments. An option with a subquerity will be something like this:

select *
 From value_occurrence
 Where peopleid = (
 SELECT FATHER.
  From value_occurrence
  Where Father Is Not Null
  GROUP by Father.
  Order by Count (*) DESC
  Limit 1.
);

If you need to withdraw all fathers with the maximum number of children, the request becomes somewhat more complicated:

with FatherCounts AS (
 SELECT FATHER.
    , Count (*) AS COUNT
  From value_occurrence
  Where Father Is Not Null
  GROUP by Father.
),
FatherRanks AS (
 SELECT FATHER.
    , Rank () Over (Order by Count Desc) AS Rank
  From Fathercounts.
)
Select value_occurrence. *
 From value_occurrence
 Join FatherRanks.
  ON PEPLEID = FatherRanks.Father
 Where rank = 1;

Again, option without Join s:

- ...
SELECT *
 From value_occurrence
 Where Peopleid in (
 SELECT FATHER.
  From FatherRanks.
  WHERE RANK = 1
);

Answer 2, Authority 100%

Option 1 Use Inner Join to select only connected lines Exclude zero records:

select f2.name, f1.father, count (f1.father) as value_occurrence
From Family F1.
JOIN FAMILY F2 ON F1.FATHER = F2.PEOPLEID
Group by F1.Father, F2.Name
Order by Value_OscCurrence DESC Limit 1;

Option 2 Make SELECT from two tables where the Father column is equal to the PEOPLEID column:

select f2.name, f1.father, count (f1.father) as value_occurrence
From Family F1, Family F2 WHERE F1.FATHER = F2.PeopleID
Group by F1.Father, F2.Name
Order by Value_OscCurrence DESC Limit 1;

Check required, currently there is no database.