1

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
2

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 >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 2 MONTH
    AND p.createdAt  < LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 1 MONTH
GROUP BY c.id, c.user
ORDER BY totalInvited DESC ;
1

I solved my problem with this Query:

I know its not perfect, but works like a charm...

SELECT
    COUNT(p.user) AS totalInvited,
    c.user AS clientName
FROM client p 
INNER JOIN client c ON p.idFather = c.id 
WHERE MONTH(p.createdAt) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND YEAR(p.createdAt) = YEAR(NOW())
GROUP BY clientName
ORDER BY totalInvited DESC

Hope it helps someone else...

  • You can mark your own answer as correct after24 hours - might also help others. You don't get points though! :-( – Vérace Jul 4 '16 at 2:42
  • You will have problems with your year / month logic in January. – Michael Green Jul 4 '16 at 10:43
0

You can try this :

select 
a.id,
a.user,
a.idFather,
a.createdAt,
(select count(id) from client t where t.idFather = a.id and month(t.createdAt) = month(a.createdAt)) as cnt
from client a
where month(a.createdAt) = month(curdate()) ;
  • This would be a better answer if you explained a few details to the poster of the question.. – RLF Jul 4 '16 at 17:24
  • @RLF I thought it's a simple query so i did not explain each line of the query. And i did not use year filtering in this query, for that he can use one more filter condition along with month like where year(a.createdAt) = year(curdate()). – Nikesh Kedlaya Jul 5 '16 at 12:47
  • The point of my comment was: You just offered code without any explanation. This will greatly reduce the likelihood that your answer will receive much attention. – RLF Jul 11 '16 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.