CODEWITHSUNDEEP
mysqlsql
user2320010
user2320010

Reputation: 95

Display results which have no count/zero as well

I am trying to get a count of the number of logins during a given timeframe, currently my SQL query displays only results that had at least one login, I'd like it to display even those which have zero logins.

Query i'm using:

SELECT c.FullName, COUNT(l.Id) 
FROM LoginsTable l JOIN UsersTable u ON u.Email = l.Email JOIN Organisations c ON c.Id = u.OrganisationId 
WHERE l.AttemptTime > "2019-10-01" AND l.AttemptTime < "2019-11-01" AND l.Success = 1 
GROUP BY c.Name
ORDER BY c.Name ASC;

Upvotes: 0

Views: 67

Answers (3)

Nick
Nick

Reputation: 147156

You have a few issues. Firstly, you either need to use a RIGHT JOIN from LoginsTable or reorder the JOINs to put the JOIN to LoginsTable last and use a LEFT JOIN. Given the nature of your query the latter probably makes more sense. Secondly, you need to put any conditions on fields from a table which has been LEFT JOINed into the join condition, otherwise MySQL converts the LEFT JOIN into an INNER JOIN (see the manual). Finally, you should GROUP BY the same fields as specified in your SELECT. This should work:

SELECT c.FullName, COUNT(l.Id) 
FROM Organisations c
JOIN UsersTable u ON u.OrganisationId = c.Id
LEFT JOIN LoginsTable l ON u.Email = l.Email AND l.AttemptTime > "2019-10-01" AND l.AttemptTime < "2019-11-01" AND l.Success = 1 
GROUP BY c.FullName
ORDER BY c.FullName

Upvotes: 2

Gosfly
Gosfly

Reputation: 1300

As Roman Hocke said you need to use left join as below :

SELECT c.FullName, COUNT(l.Id) 
FROM UsersTable u
JOIN Organisations c ON c.Id = u.OrganisationId 
LEFT JOIN LoginsTable l ON u.Email = l.Email 
WHERE l.AttemptTime > "2019-10-01" AND l.AttemptTime < "2019-11-01" AND l.Success = 1 
GROUP BY c.Name
ORDER BY c.Name ASC;

Moreover, you should fix your group by or select using the same field : SELECT c.Name or GROUP BY c.FullName ORDER BY c.FullName

EDIT : Nick's answer is the one. As he said perfectly well, you need to put your conditions in the on clause of your left join. 

SELECT c.FullName, COUNT(l.Id) 
FROM UsersTable u
JOIN Organisations c ON c.Id = u.OrganisationId 
LEFT JOIN LoginsTable l ON (u.Email = l.Email AND l.AttemptTime > "2019-10-01" AND l.AttemptTime < "2019-11-01" AND l.Success = 1)
GROUP BY c.FullName
ORDER BY c.FullName ASC;

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

I found 2 issues here:

  1. your group by column is not listed on your column
  2. date condition is using double quotes.

try below query.

SELECT c.FullName, COUNT(l.Id) 
FROM LoginsTable l 
LEFT JOIN UsersTable u ON u.Email = l.Email 
LEFT JOIN Organisations c ON c.Id = u.OrganisationId 
WHERE l.AttemptTime between '2019-10-01' AND '2019-11-01' AND l.Success = 1 
GROUP BY c.FullName
ORDER BY c.FullName ASC;

Upvotes: 0

Related Questions