Problem with Inner Join and sum

[Sinha's]

Hello Everybody,

I have used inner join with two tables to join them and tried to calculate the sum of rows. Now the table descriptions are-
Table1- agent_business_collection
(it stores business collection of an agent of different business packages)

Table2 - agent_business_commission
(it stores commission of an agent against different business packages)

Now, I want to calculate total business collection and commission for a particular agent. I am using the following query to do the task.
select agent_business_collection.agent_code,agent_business_collection.business_pkg_ref,sum(agent_business_collection.self_amount),sum(agent_business_commission.self_comm_amt) from agent_business_collection inner join agent_business_commission on agent_business_collection.business_pkg_ref=agent_business_commission.business_pkg_ref where agent_business_collection.agent_code='100004' group by agent_business_collection.agent_code,agent_business_collection.business_pkg_ref

But after executing, it returns garbage total. What I am missing and what wrong I have did? Please help me.

[Renukavani]

Might be agent_business_collection.business_pkg_ref is unique data bcz of this u r getting the garbage total when u r trying do to the sum(agent_business_collection.self_amount),sum(agent_business_commission.self_comm_amt).

Try to remove the agent_business_collection.business_pkg_ref from the select statement.

[woodsrr]

Just a guess but are the self_amount summing correctly and the self_comm_amt failing to sum correctly?

I think the easy solution would be to create a view to presum self_comm_amt. There are other ways but this would be simple.