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

4 Answers 4

4

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...

2
  • You can mark your own answer as correct after24 hours - might also help others. You don't get points though! :-(
    – Vérace
    Commented Jul 4, 2016 at 2:42
  • You will have problems with your year / month logic in January. Commented Jul 4, 2016 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()) ;
3
  • This would be a better answer if you explained a few details to the poster of the question..
    – RLF
    Commented Jul 4, 2016 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()). Commented Jul 5, 2016 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
    Commented Jul 11, 2016 at 13:06
0

I would like to add HAVING totalInvited > 0 to the accepted improved answer. There's no need to return rows with no invite.

Below will be the complete query:

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
HAVING totalInvited > 0
ORDER BY totalInvited DESC;

I hope the simple modification helps someone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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