Member Avatar for 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.

  • 3 Contributors
  • 2 Replies
  • 188 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by woodsrr

Dani AI

Generated

Inflated or “garbage” sums most often come from row-multiplication caused by joining before you aggregate. If one table has multiple rows for the same package, the matching rows from the other table are repeated in the join and SUM overcounts. Also: grouping by package yields per-package totals, not a single agent total — that combination is a common source of confusion.

A robust fix is to aggregate each table first, then join the aggregated results. This prevents duplication entirely and also makes it clear whether totals are per-package or per-agent. Example (per-package totals for agent '100004'):

-- aggregate each table first, then join by agent + package
SELECT co.agent_code,
       co.business_pkg_ref,
       co.total_collection,
       COALESCE(cm.total_commission, 0) AS total_commission
FROM (
  SELECT agent_code, business_pkg_ref, SUM(self_amount) AS total_collection
  FROM agent_business_collection
  WHERE agent_code = '100004'
  GROUP BY agent_code, business_pkg_ref
) co
LEFT JOIN (
  SELECT agent_code, business_pkg_ref, SUM(self_comm_amt) AS total_commission
  FROM agent_business_commission
  WHERE agent_code = '100004'
  GROUP BY agent_code, business_pkg_ref
) cm
  ON co.agent_code = cm.agent_code
  AND co.business_pkg_ref = cm.business_pkg_ref;

If the goal is a single total per agent, either group only by agent_code or aggregate each table by agent and then join. A quicker but less robust change is to include agent_code in the JOIN so rows for other agents aren’t mixed in, e.g.:

-- join on both agent and package (still vulnerable if either side has multiple rows per package)
SELECT c.agent_code,
       SUM(c.self_amount) AS total_collection,
       SUM(cm.self_comm_amt) AS total_commission
FROM agent_business_collection c
INNER JOIN agent_business_commission cm
  ON c.business_pkg_ref = cm.business_pkg_ref
  AND c.agent_code = cm.agent_code
WHERE c.agent_code = '100004'
GROUP BY c.agent_code;

For debugging: compare the independent sums from each table (SUM(...) WHERE agent_code='100004') to the join result to see if duplication occurs. Check for duplicate rows in commission or collection with a GROUP BY ... HAVING COUNT(*) > 1. ’s point about changing the grouping affects granularity, and ’s suggestion to pre-sum (for example via a view) is exactly the pragmatic, maintainable approach here.

Recommended Answers

All 2 Replies

Member Avatar for 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.

Edited by Renukavani because: n/a
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.