CODEWITHSUNDEEP
sqlsql-server
Marcin Ł.
Marcin Ł.

Reputation: 45

Subtract results of a SUM() query from values from another table

I'm creating a stored procedure that calculates remaining assets based on a table containing an inventory and another containing used assets. For each ID_Product I need to subtract a sum of all rows from an amount from the other table. I've had unexpected results, probably because the query analizes rows in an unexpected order.

This is what I've tried:

SELECT 
    inv_user.ID_Product As 'Product ID', 
    SUM(inv_user.Amount) - SUM(inv_robotparts.Amount) As 'Amount'
FROM 
    inv_robotparts 
full join 
    inv_user 
ON 
    inv_user.id_product = inv_robotparts.id_product  
WHERE 
    inv_user.id_user = 4
GROUP BY 
    inv_user.id_product

The structure is as follows: inv_user

inv_user

inv_robotparts

inv_robotparts

My expected results:

ID_Product    Amount
0             1
1             10
14            -543
...

I need only results from a single ID_User. An ideal sollution would also show products that are used, but weren't in the inventory with a negative amount. All advice is appreciated, since I am new to SQL. I'm on SQL Server 2012.

Upvotes: 0

Views: 1914

Answers (3)

MtwStark
MtwStark

Reputation: 4058

This is another way to sum your inventory data and to filter only products in inv_user table. If you want also negative numbers delete HAVING clause (last line)

select id_user, id_product, sum(amount) amount
from 
    (
    select id_user, id_product, -amount amount, 0 used from inv_robotparts
    union all
    select id_user, id_product, amount, 1 used from inv_user
) t
where id_user=4 
group by id_user, id_product
having sum(used) >0

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10711

You get duplicates like this. One possible solution is to separate the GROUP BY into two separate queries or use subqueries behind SELECT.

SELECT coalesce(t1.ID_Product, t2.ID_Product) as ID_Product, 
       coalesce(t1.user_sum,0) - coalesce(t2.robot_sum,0) as amount
FROM
(
    SELECT 
        inv_user.ID_Product,
        SUM(inv_user.Amount) user_sum
    FROM inv_user 
    WHERE inv_user.id_user = 4
    GROUP BY inv_user.id_product
) t1
FULL JOIN
(
    SELECT inv_robotparts.ID_Product,
        SUM(inv_robotparts.Amount) robot_sum
    FROM inv_robotparts 
    WHERE inv_robotparts.id_user = 4
    GROUP BY inv_robotparts .id_product
) t2 ON t1.ID_Product = t2.ID_Product

Upvotes: 1

Eralper
Eralper

Reputation: 6622

Could you try this CTE query?

; with u as (
    select ID_Product, sum(Amount) Amount from inv_user group by ID_Product
), r as (
    select ID_Product, sum(Amount) Amount from inv_robotparts group by ID_Product
)
select
    isnull(u.ID_Product, r.ID_Product) as ID_Product, isnull(sum(u.Amount),0) - isnull(sum(r.Amount),0)
from u
full join r on u.ID_Product = r.ID_Product
group by isnull(u.ID_Product, r.ID_Product)

The output is as follows

enter image description here

ID_User can be added to query as follows

; with u as (
    select ID_Product, ID_User, sum(Amount) Amount from inv_user group by ID_Product, ID_User
), r as (
    select ID_Product, ID_User, sum(Amount) Amount from inv_robotparts group by ID_Product, ID_User
)
select
    isnull(u.ID_Product, r.ID_Product) as ID_Product, 
    isnull(u.ID_User, r.ID_User) as ID_User,
    isnull(sum(u.Amount),0) - isnull(sum(r.Amount),0) Amount
from u
full join r
    on u.ID_Product = r.ID_Product and
       u.ID_User = r.ID_User
group by isnull(u.ID_Product, r.ID_Product), isnull(u.ID_User, r.ID_User)

Upvotes: 0

Related Questions