CODEWITHSUNDEEP
sqlsql-server
Mike Baxter
Mike Baxter

Reputation: 7268

Query SUM of value from joined table - returns multiplied result

In my database, I have the table: Job. Each job can contain tasks (one to many) - and the same task can be re-used on multiple jobs. Therefore there is a Task table and a JobTask (junction table for the many-to-many relationship). There is also the Payment table, that records payments received (with a JobId column to track which job the payment is related to). Potentially there could be more than one payment attributed to a job.

Using SQL Server 2012, I have a query that returns a brief summary of jobs (total value of the job, total amount received):

select j.JobId,
sum(t.Rate) as [TotalOwedP],
sum(p.Amount) as [TotalReceivedP]

from Job j
left outer join Payment p on j.JobId=p.JobId
left outer join JobTask jt on j.JobId=jt.JobId
left outer join Task t on jt.TaskId=t.TaskId

group by j.JobId

The problem with this query is that it's returning a much higher amount for "total received" than it should be. There must be something I'm missing here to cause this.

In my test database, I have one job. This job has six tasks assigned to it. It also has one payment assigned to it (£100 - stored as 10000).

Using the above query on this data, the TotalReceivedP column comes to 60000, not 10000. It seems to be multiplying the payment amount for each task assigned to the job. Lo and behold, if I add another task to this job (so the number of tasks is now 7), the TotalReceivedP column shows 70000.

There is a definite problem with my query, but I just can't work out what it is. Any keen eyes able to spot it? Seems to be something wrong with the join.

Upvotes: 0

Views: 117

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

For each separate JobId, SUM(p.Amount) sums up the same Amount value for every Task record related to a Job record with this JobId. If 6 records are related to a specific Job, then SUM(p.Amount) will calulate the amount multiplied by 6, if 7 records are related, then the amount is multiplied by 7 and so on.

Since for each Job there is only one Payment amount, there is no need to perform a sum on p.Amount. Sth like this will give you the desired result:

select j.JobId,
       sum(t.Rate) as [TotalOwedP],
       max(p.Amount) as [TotalReceivedP]

from #Job j
left outer join #Payment p on j.JobId=p.JobId
left outer join #JobTask jt on j.JobId=jt.JobId
left outer join #Task t on jt.TaskId=t.TaskId

group by j.JobId

EDIT:

Since the platform used is SQL Server a very neat way (IMHO) to get sum aggregates is CTEs:

;WITH TaskGroup AS (
   SELECT JobId, SUM(Rate) AS [TotalOwedP]
   FROM #Task t
   INNER JOIN #JobTask jt ON t.TaskId = jt.TaskId
   GROUP BY JobId
), PaymentGroup AS (
   SELECT JobId, SUM(Amount) [TotalReceivedP]
   FROM #Payment
   GROUP BY JobId
)
SELECT tg.JobId, tg.TotalOwedP, pg.TotalReceivedP
FROM TaskGroup tg
LEFT JOIN PaymentGroup pg ON tg.JobId = pg.JobId

I am only guessing about the tables schema, but the above should give you what you want. The first CTE calculates Rate sums per JobId, the second CTE Amount sums per JobId, the final query uses both CTEs to put the results together in a single table.

Upvotes: 1

Antonne Tulloch
Antonne Tulloch

Reputation: 33

You should separate the query for the TotalOwed from the query for the TotalReceived. The TotalOwed is based on the individual tasks whereas the payment is linked to the job and not the task.

--first query
select j.JobId,
sum(t.Rate) as [TotalOwedP]
from Job j
left outer join JobTask jt on j.JobId=jt.JobId
left outer join Task t on jt.TaskId=t.TaskId
group by j.JobId

--second query
select j.JobId,
sum(p.Amount) as [TotalReceivedP]
from Job j
left outer join Payment p on j.JobId=p.JobId
group by j.JobId

Upvotes: 0

RobP
RobP

Reputation: 9522

the problem is that your JOIN constraint must not be specific enough so you are getting too many result rows. Probably payments should be joined on Task as well? Impossible to tell unless you post all table schemas.

Upvotes: 0

Dirk Huber
Dirk Huber

Reputation: 912

Use a Sub-Select to calculate the SUM:

select j.JobId,
sum(t.Rate) as [TotalOwedP],
(SELECT SUM(p.Amount) FROM Payment p WHERE j.JobId=p.JobId) as [TotalReceivedP]
from Job j
left outer join JobTask jt on j.JobId=jt.JobId
left outer join Task t on jt.TaskId=t.TaskId
group by j.JobId

Upvotes: 2

Related Questions