Problem w tym, że podzapytanie musi pytać o konkretne id z workers, czyli musisz dodać warunek:
SELECT first_name,
last_name,
COUNT(scheduleevents.id),
(SELECT COUNT(scheduleevents.id)
FROM workers as w
LEFT JOIN scheduleevents
ON scheduleevents.worker_id = w.id
AND scheduleevents.is_start_time = 1
AND scheduleevents.is_active = 0
AND strftime('%Y', DATE) = strftime('%Y', DATE())
AND ( scheduleevents.DATE < DATE()
OR ( scheduleevents.DATE = DATE()
AND scheduleevents.TIME < TIME() ) )
WHERE
w.id = workers.id
GROUP BY workers.id)
FROM workers
LEFT JOIN scheduleevents
ON scheduleevents.worker_id = workers.id
AND scheduleevents.is_start_time = 1
AND scheduleevents.is_active = 0
AND ( scheduleevents.DATE < DATE()
OR ( scheduleevents.DATE = DATE()
AND scheduleevents.TIME < TIME() ) )
GROUP BY workers.id
Zwróć uwagę n alias "w" dla tabeli workers w podzapytaniu, dzieki temu warunek WHERE w.id = workers.id, sprawia że podzapytanie odwołuje się do wiersza w którym jest wykonywane.
W tym przypadku podzapytanie nie wymaga w ogóle join-a:
SELECT first_name,
last_name,
COUNT(scheduleevents.id),
(SELECT COUNT(scheduleevents.id)
FROM scheduleevents
WHERE
AND scheduleevents.is_start_time = 1
AND scheduleevents.is_active = 0
AND strftime('%Y', DATE) = strftime('%Y', DATE())
AND ( scheduleevents.DATE < DATE()
OR ( scheduleevents.DATE = DATE()
AND scheduleevents.TIME < TIME() ) )
scheduleevents.worker_id = workers.id
)
FROM workers
LEFT JOIN scheduleevents
ON scheduleevents.worker_id = workers.id
AND scheduleevents.is_start_time = 1
AND scheduleevents.is_active = 0
AND ( scheduleevents.DATE < DATE()
OR ( scheduleevents.DATE = DATE()
AND scheduleevents.TIME < TIME() ) )
GROUP BY workers.id
Alternatywa to użycie wyrażenia case do obliczenia ilości w bieżącym roku:
SELECT first_name,
last_name,
COUNT(scheduleevents.id) ,
SUM(case when strftime('%Y', DATE) = strftime('%Y', DATE()) then 1 else 0 end)
FROM workers
LEFT JOIN scheduleevents
ON scheduleevents.worker_id = workers.id
AND scheduleevents.is_start_time = 1
AND scheduleevents.is_active = 0
AND ( scheduleevents.DATE < DATE()
OR ( scheduleevents.DATE = DATE()
AND scheduleevents.TIME < TIME() ) )
GROUP BY workers.id
wyrażenie case when strftime('%Y', DATE) = strftime('%Y', DATE()) then 1 else 0 end dla bieżącego roku da 1 dla każdego innego 0, więc sumujemy ani liczymy.