group by access seperate column

Question

I'm very new to SQL and have the following (seemingly simple) database query.

I have a table containing all timetracking information for a set of employees:

date        employeeId timeIn timeOut
01/01/20    1355       12:00  4:00
01/01/20    1355       4:30   6:00
01/01/15    1234       9:00   12:00
02/01/15    5555       5:00   6:00
02/01/15    1111       1:00   4:00

foreach date, I want to count the number of unique employees that worked, ending with a table like:

date      totalEmployeesWorked
01/01/15  2
02/01/15  2

There are several Id's I would like to ignore as well. So far I came up with:

I have tried the following query in MS Access 2003:

SELECT [TimeTrackingLog-sql].date, COUNT(DISTINCT callerName)
FROM [TimeTrackingLog-sql]
GROUP by [TimeTrackingLog-sql].date

and it complained about a 'missing operator', highlighting my use of callerName.

Answer 1

The good news is that your query is perfectly legal (and straight forward!) ANSI SQL, that will work on any sensible database. The bad news is that MS Access which you're using does not support this syntax.

It can be worked around with a subquery, though:

SELECT   t.date, COUNT(*)
FROM     (SELECT DISTINCT [TimeTrackingLog-sql].date, 
                          [TimeTrackingLog-sql].callerName
          FROM   [TimeTrackingLog-sql]) t
GROUP BY t.date

Answer 2

This will work:-

SELECT date, count(distinct `employeeId`) FROM `table_name` group by date;