MSSQL Count

I am trying to write a query that counts records from two tables, but am
getting wrong counts. Basically what i want is this:
SELECT Count(callSched.callID) as callCount, Count(visitSched.visitID) as
visCount, employees.workerNumber, employees.workerName
From employees LEFT OUTER JOIN
callSched on employees.workerNumber = callSched.empID LEFT OUTER JOIN
visitSched on employees.workerNumber = visitSched.wrkID
GROUP BY workerNumber, workerName

I get the proper columns grouped, but am getting 400,000 as the count when it
should be 1500-2000.
Any ideas how to make this return the results?

MikeL7 wrote:

I am trying to write a query that counts records from two tables, but am
getting wrong counts. Basically what i want is this:
SELECT Count(callSched.callID) as callCount, Count(visitSched.visitID) as
visCount, employees.workerNumber, employees.workerName
From employees LEFT OUTER JOIN
callSched on employees.workerNumber = callSched.empID LEFT OUTER JOIN
visitSched on employees.workerNumber = visitSched.wrkID
GROUP BY workerNumber, workerName

I get the proper columns grouped, but am getting 400,000 as the count when it
should be 1500-2000.
Any ideas how to make this return the results?

I don't think a GROUP BY will work in this situtation, what might work
is 2 sub querys, possibly...

SELECT COUNT(SELECT .... ) AS callCount, COUNT(SELECT ....) AS visCount

But I can't be sure.

Steve

MikeL7 wrote:

I am trying to write a query that counts records from two tables, but am
getting wrong counts. Basically what i want is this:
SELECT Count(callSched.callID) as callCount, Count(visitSched.visitID) as
visCount, employees.workerNumber, employees.workerName
From employees LEFT OUTER JOIN
callSched on employees.workerNumber = callSched.empID LEFT OUTER JOIN
visitSched on employees.workerNumber = visitSched.wrkID
GROUP BY workerNumber, workerName

I get the proper columns grouped, but am getting 400,000 as the count when it
should be 1500-2000.
Any ideas how to make this return the results?

I don't think a GROUP BY will work in this situtation, what might work
is 2 sub querys, possibly...

SELECT COUNT(SELECT .... ) AS callCount, COUNT(SELECT ....) AS visCount

But I can't be sure.

Steve

I have also done the query seperate and used a union all clause and get the
right results, but the employees are listed twice and the counts are all in the
same column instead of two different one named after their alias.

I have also done the query seperate and used a union all clause and get the
right results, but the employees are listed twice and the counts are all in the
same column instead of two different one named after their alias.

MikeL7 wrote:

I have also done the query seperate and used a union all clause and get the
right results, but the employees are listed twice and the counts are all in the
same column instead of two different one named after their alias.

I can't see using a UNION would help, you need to do something like I
suggested, did that not work?

Steve

MikeL7 wrote:

I have also done the query seperate and used a union all clause and get the
right results, but the employees are listed twice and the counts are all in the
same column instead of two different one named after their alias.

I can't see using a UNION would help, you need to do something like I
suggested, did that not work?

Steve