COUNTING results per row in self referenced table I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.

Sample:

id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016


I'm trying to count how many clients each client invited to the system this month.

I'm using this query to count, but I'm doing the counting using PHP. I want to do it in MySQL.

SELECT COUNT(*) FROM client
    WHERE idFather = 1
    AND MONTH(createdAt) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)


For now, I'm doing it like this:

Get all results from database, and for each client, executing the query above. I'm looking to execute a query and get it counted from the database without the need look at each one with PHP. Today I have just 60 clients in the table, but next month, it can be 1 thousand, or more. It will not work properly with php.

Thanks for the help!

EDIT:
I'm looking to get a query result like this:

id | user    | idFather | createdAt  | totalInvited
---+---------+----------+------------+-------------
01 | raphael | null     | 01-06-2016 | 2     
02 | jose    | 01       | 03-06-2016 | 0
03 | ryan    | 01       | 02-06-2016 | 1
04 | alyssa  | 03       | 04-06-2016 | 0
Your answer is on the right track but needs several adjustments to be correct and efficient:


The INNER JOIN will only give you clients with 1 or more children, not the ones with 0 children. You need an outer join for that: client c LEFT JOIN client p ...
If there is a UNIQUE constraint on client (user), then the group by is ok. Otherwise you may have 2 users with same name (but different id). To differentiate them, GROUP BY c.id, c.user is needed, which is not bad to have anyway for efficiency.  
The date math and conditions are not entirely correct, will fail on January dates. They need correction. Even better to make the conditions sargable so indexes on createdAt may be used.
If the date conditoins are to be checking the children createdAt timestamps, then they need to be move to the ON clause.


The query improved with all the above points:

SELECT
    COUNT(p.idFather) AS totalInvited,
    c.user AS clientName
FROM client c
  LEFT JOIN client p 
    ON  p.idFather = c.id 
    AND p.createdAt &gt;= LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 2 MONTH
    AND p.createdAt  &lt; LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 1 MONTH
GROUP BY c.id, c.user
ORDER BY totalInvited DESC ;