error-in-the-execution-of-the-query-in-sql-server-when-I-am-using-sum-function

[former_member1062654]

here is my database record

and when i am using SUM function to group by productID

AC00001 total amount should be 1500

but it show 2000

may anyone help me and teach me how to use sum to join the muti-table and sort it at productID?

and final I should use update/ insert into the result into the product table for saving at the database like this
STOCKAmount

Thanks a lot !!!!!!!!!!!!!!!!!!!!!

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

As you're looking to get most out of your community membership, please consider include a profile picture to increase user engagement & additional resources to your reference that can really benefit you:

• Profile https://developers.sap.com/tutorials/community-profile.html
• Tips for Questions: https://community.sap.com/resources/questions-and-answers
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,

-Alex

[chris_keating]

I think the schema is complicating matters. A consolidated table representing Stock with columns for LocationType , change the Store/Warehouse IDs to LocationID, ProductID and Amount would greatly simplify the query requirements. You could implement a view for that purpose. Alternatively, a derived table can help. Note the examples below are written in SQL Anywhere.

View:
create view vStock( )
AS
    select 'Store', StoreID as LocationId, ProductID, Amount
    from StoreStock
        union
    select 'Warehouse', WarehouseID as LocationId, ProductID, Amount
    from WarehouseStock
go


-- vStock can also be a base table consolidating Store and Warehouse stock tables
select 
    ProductId, sum(Amount) 
from vStock
group by
    ProductId;

Derived Table:
select dt.ProductId, sum(StockAmt)
from
    (
        select ProductId, sum(Amount) as StockAmt
         from WarehouseStock ws
        group by ProductId
            union
        select ProductId, sum(Amount) as StockAmt
         from StoreStock ws
        group by ProductId
    ) dt
group by dt.ProductId;

You should be able to adapt the update based on the answers in your other recent question.

[davidalexandre]

Olá!

Veja se ajuda!

select 
t0.ProductID, 
(sum(t0.Amount) + sum(t1.Amount)) as "TotalAmount"
from warehousestock t0 
inner join storestock t1 on t1.ProductID = t0.ProductID 
group by 
t0.ProductID