Dear Friends
I have Table contain
Unless I am missing something there is not enough information in the first table to be able to return the result in the second table.
Now if it was the other way around you could sum the ExchangeQuantity based on grouping the ID and get the result of 5 (exchangeQuantity) for ID 4 as well as the other rows.
A picture of data is useless to us. We need CREATE TABLE and INSERT statements so we can test and run the code you want us to write for you.
1 Like
CREATE TABLE Test (ID INT , AddQuantity DECIMAL (18,5), ExchangeQuantity DECIMAL (18,5) , RefID INT )
INSERT INTO dbo.Test (ID,AddQuantity,ExchangeQuantity,RefID)
SELECT 1, 10,0,NULL
UNION
SELECT 2, 10,0,NULL
UNION
SELECT 3, 0,8,NULL
UNION
SELECT 4, 0,5,NULL
UNION
SELECT 5, 0,1,NULL
Hi Samir,
Thanks for the statements but you just cannot get the result set you are after from the source table without making up figures, I suspect you have this the wrong way round and that what you think is your result table is in fact your source table.
Based on my thoughts the following would give you a set of results by swapping the tables round that looks similar.
-- Set up a new test table
CREATE TABLE Test2 (ID INT , AddQuantity DECIMAL (18,5), ExchangeQuantity DECIMAL (18,5) , RefID INT )
--insert the source data
INSERT INTO dbo.Test2 (ID,AddQuantity,ExchangeQuantity,RefID)
VALUES
(1, 10,0,NULL),
(2, 10,0,NULL),
(3, 0,8,1),
(4, 0,2,1),
(4, 0,3,2),
(5, 0,1,NULL);
-- see what we have (it looks like your result table)
SELECT * FROM [dbo].[Test2];
-- and finally a SELECT with GROUP and SUM to bring the two values for ID 4 into one total
SELECT
ID,
AddQuantity,
SUM(ExchangeQuantity)
FROM dbo.test2
GROUP BY ID, AddQuantity
ORDER BY ID ASC;
I have not included the RefID column as they are all NULL and do not appear relevant.
I have to state though, I may have completely missinterpreted but just putting in thoughts for what I can see.