CODEWITHSUNDEEP
mysqlsql
Marc
Marc

Reputation: 29

Mysql Count distinct users in a table

I need to group by date the records in the log table, and for that specific day the number of accesses by a regualar employee or specialEmployee. The accesses should be distinct for example if a type of user logged in twice, it should only be counted once for that day

Type
PK typeId
name

Employee
PK empId
firstName
lastName
FK typeId     


Log
PK logId
FK empId
date

I've tried the query below, but to no avail. When I replace the sum/case with count it works but then I can't specify which type of user to count distinctly

SELECT L.`date`
SUM(DISTINCT CASE WHEN E.typeId =1 THEN 1 ELSE 0 END) as specialEmployeeAccesses,
SUM(DISTINCT CASE WHEN E.typeId=2 THEN 1 ELSE 0 END) as regularEmployeeAccesses
FROM LOG L
INNER JOIN
Employee E
ON E.empId = L.empId
GROUP BY L.`date`

Upvotes: 1

Views: 137

Answers (1)

user359040
user359040

Reputation:

Try:

SELECT L.`date`,
       COUNT(DISTINCT CASE WHEN E.typeId=1 THEN E.empId END) 
            as specialEmployeeAccesses,
       COUNT(DISTINCT CASE WHEN E.typeId=2 THEN E.empId END) 
            as regularEmployeeAccesses
FROM LOG L
INNER JOIN Employee E
ON E.empId = L.empId
GROUP BY L.`date`

Upvotes: 1

Related Questions