How can I use two or more COUNT()s in one SELECT statament?

Question

i develop this code:

SELECT COUNT(NewEmployee.EmployeeID), NewEmployee.EmployeeId,EmployeeName 
FROM NewEmployee INNER JOIN NewTimeAttendance  
  ON NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID 
  and NewTimeAttendance.TotalTime is  null 
  and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null)
  and (month = 1 or month = 2 or month = 3)  
GROUP BY NewEmployee.EmployeeID, EmployeeName
order by EmployeeID

from my previous two questions selecting null stuff and counting issue...that amazing code is working beautifully fine..but now i need to select more than one count...

...searched (google) .... found alias...tried:

SELECT COUNT(NewEmployee.EmployeeID) as attenddays, COUNT(NewEmployee.EmployeeID) as empabsent
 , NewEmployee.EmployeeId,EmployeeName 
 FROM NewEmployee INNER JOIN NewTimeAttendance  
  ON empabsent =NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID 
  and NewTimeAttendance.TotalTime is  null 
  and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null )
    and (month=1 or month =2 or month = 3) , 
    attenddays = NewTimeAttendance.EmployeeID 
  and NewTimeAttendance.TotalTime is  null 
  and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null )
    and (month=1 or month =2 or month = 3)
GROUP BY NewEmployee.EmployeeID, EmployeeName  order by EmployeeID

Incorrect syntax near '='.

second try:

SELECT COUNT(NewEmployee.EmployeeID) as attenddays, COUNT(NewEmployee.EmployeeID) as absentdays,
NewEmployee.EmployeeId,EmployeeName 
 FROM NewEmployee INNER JOIN NewTimeAttendance  
  ON attenddays(NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID 
  and NewTimeAttendance.TotalTime is  null 
  and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null )
    and (month=1 or month =2 or month = 3)) ,
    absentdays(NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID 
  and NewTimeAttendance.TotalTime is  null 
  and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null )
    and (month=1 or month =2 or month = 3))
GROUP BY NewEmployee.EmployeeID, EmployeeName  order by EmployeeID

Incorrect syntax near '='.

not very good ideas... so ...help

all i want is to count the ids with diferent conditions...

COUNT(NewEmployee.EmployeeID)

ON NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID and NewTimeAttendance.TotalTime is null and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null ) and (month=1 or month =2 or month = 3)

and

COUNT(NewEmployee.EmployeeID)

ON NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID and NewTimeAttendance.TotalTime is not null and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null ) and (month=1 or month =2 or month = 3)

in the same select statment.

thanks in advance

Answer 1

The first query throws a syntax error because of a bunch of errors in the ON clause. To wit:

1. empabsent =NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID 
2. and (month=1 or month =2 or month = 3) , attenddays = NewTimeAttendance.EmployeeID 

In the second query, are AttendDays and AbsentDays supposed to be user-defined functions? If they are, I cannot imagine that syntax working in any dialect of SQL.

What exactly are you trying to achieve? You want to counts of what? Can you provide two different SQL statements that independently give you the counts you want and then we might try combining them?

EDIT Given the new information you should be able to do this in a single query:

SELECT EmployeeId, EmployeeName
    , Sum(Case When NewTimeAttendence.TotalTime Is Null Then 1 Else 0 End) As AttendDays
    , Sum(Case When NewTimeAttendence.TotalTime Is Not Null Then 1 Else 0 End) As AbsentDays
FROM NewEmployee
    Join NewTimeAttendence
        On NewTimeAttendence.EmployeeId = NewEmployee.EmployeeeId
Where (NewTimeAttendence.Note = '' Or NewTimeAttendence.Note Is Null)
    And Month In(1,2,3)
Group By EmployeeId, EmployeeName

Answer 2

It looks like you are trying to find out how many days each employee was in attendance and absent. I would recommend a query like this:

SELECT EmployeeId, EmployeeName,
    (SELECT COUNT(*)
     FROM NewTimeAttendance
     WHERE NewTimeAttendance.EmployeeID = NewEmployee.EmployeeId
         /* include condition for employee in attendance */
         AND TotalTime IS NULL
         AND (Note = '' or Note IS NULL)
         AND Month BETWEEN 1 AND 3) AS AttendDays,
    (SELECT COUNT(*)
     FROM NewTimeAttendance
     WHERE NewTimeAttendance.EmployeeID = NewEmployee.EmployeeId
         /* include condition for employee absent */
         AND TotalTime IS NULL
         AND (Note = '' or Note IS NULL)
         AND Month BETWEEN 1 AND 3) AS AbsentDays
FROM NewEmployee
ORDER BY EmployeeID