SUM / GROUP BY in SELECT, Subtracting fields in two tables

bruceg

I have a query, that displays issued and returned weights for a dispensing system. I would like to be able to combine the two results, giving me the total between the issue and returned weight.

I have two tables: issue, and return. If I have the following values, how would my SELECT statement look?

issue:

job code weight

1 A 20
1 B 30
1 C 40
1 D 10

return:

job code weight

1 A 10
1 C 5
1 D 8

So, I would like my SELECT statement to return these values:

job code total used

1 A 10
1 B 30
1 C 35
1 D 2

I currently have a SELECT statement like this:

SELECT issue.job, issue.code, return.job, return.code, SUM(issue.weight) AS iss_weight, SUM(return.weight) AS rtn_weight

FROM issue LEFT JOIN return ON issue.job=return.job

WHERE issue.job = $job_id

GROUP BY issue.code

It does not give me the output I am looking for. Can anyone help?

TIA...

Bruce

Lars_Berg

SELECT issue.job, issue.code, 
issue.weight  - coalesce(return.weight,0) AS totalUsed
FROM issue LEFT JOIN return ON issue.job=return.job 
WHERE issue.job = $job_id

bruceg

I should have been clearer in my first post. I still need to SUM up the weights, for each code used. There can be multiple issues, and returns for a particular code. The job number will stay constant. So, I could have records like this:

issue:

job code weight

1 A 20
1 A 10
1 A 20
1 B 30
1 C 40
1 D 10
1 D 10

return:

job code weight

1 A 10
1 A 5
1 C 5
1 D 8

Therefore, I would need to SUM all the values for code A, which would be 50, and subtract the returns for code A, which total 15, thus leaving me with the net usage for code A on that job, of 35. This is the number I would need reported.

Thanks again!

Bruce

Lars_Berg

Yes, I suspected that

SELECT job, code, 
sum(weight)  - (select(sum(coalesce(weight,0)) from return where job = i.job and code = i.code)
FROM issue as i
WHERE job = $job_id 
group by job,code

bruceg

hmmm... Maybe MySQL does not like the subselect? I keep getting a syntax error, near the second SELECT. Is there a way to make this SELECT MySQL friendly?

I'm using 3.23.47

Lars_Berg

Ah mysql. No wonder, it doesn't matter which version you have.

The solution I see is to have two queries

SELECT job, code, sum(weight)  

FROM issue
WHERE job = $job_id 
group by job,code
order by code

SELECT issue.job, issue.code, coalesce(sum(r.weight),0)  

FROM issue left join result 
on issue.code = result.code
and issue.job = result.job
WHERE issue.job = $job_id 
group by issue.job,issue.code
order by issue.code

and loop those in parallel doing the subtraction in PHP.

bruceg

Could you please provide an example for looping two SELECT statements in parallel using PHP? I have never done that before.

Thanks again,

Bruce