Question 108604 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – sql show difference in one column based on the sum of another column on another table

I have table like following

job_id quantity
2948 0
2947 3
2949 34
2950 4

And I have another table like following

job_id quantity
1 10
2949 10
2949 20

If I run SELECT job_id, sum(quantity) FROM arr_database.test_table group by job_id;

job_id quantity
1 10
2949 30

As we can see , job_id is common in both table , I want to deduct sum of quantity which is available in second table from first table .

So the output will be like following as sum of quantity for job_id 2949 is 30 , and I dedcut that 30 from table one where job id is 2949 .

job_id quantity
2948 0
2947 3
2949 4
2950 4
Tags:

2

Answers


  1. 0 
    with    
    t1 as (select job_id, sum(quantity) as qty from table1),
    t2 as (select job_id, sum(quantity) as qty from table2)
select
    t1.job_id,
    t1.qty - coalesce(t2.qty, 0) as qty
from 
    t1
    left join
    t2 on t2.job_id = t1.job_id
    Login or Signup to reply.
  2. 0

    If the job_id is unique in the first table then you can simply do it using this arithmitic max() – sum() :

    select t1.job_id, max(t1.quantity) - sum(case when t2.quantity then t2.quantity else 0 end) as quantity
from table1 as t1
left join table2 t2 on t1.job_id = t2.job_id
group by job_id

    Demo here

    If it is not unique then do it as follows :

    select c1.job_id, c1.quantity - coalesce(c2.quantity, 0)
from (
  select job_id, sum(quantity) as quantity
  from table1
  group by job_id
) c1
left join (
   select job_id, sum(quantity) as quantity
   from table2
   group by job_id
) c2 on c1.job_id = c2.job_id

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search