Problem with Join

[sujitshukla]

Hi,

I am having 2 tables, Both are joined b EmployeeID.
One table has data from CMS in which agent name is repeated more than once based on calls handled for different skills, so for one agent it is possible to have 5-6 entries for 1 day. the second table has number of sales done by each agent and it will have total sales for each agent so only 1 entry per day per agent.



I am trying to create a query so that it will give me total calls and total sales done by an agent.but i am getting incorrect result.
Attaching an example file.

[weekend00]

Assume the EmployeeID which you use for join is agent's ID.( if not, you should not join on this field).

In this posting, assume that for every call in callTable there is a totalsales in agent table, and vice versa.

Following query will give the totals by date:
Query: totalsByDate
select b.EmployeeID, [Date],b.totalSales, count(a.employeeID) as totalCall from callTable as a inner join agentTable as b on a.employeeID=b.employeeID group by b.employeeID,b.totalsales,[Date]

If you want grant total, use this one( this query use totalsByDate as data source):
select EmployeeID, sum(totalSales) as GrandTotalSales, sum(totalCall) as grandTotalCall from totalsByDate group by b.employeeID