Aggregate Query Problem

Hey there,
In a simple project and among my datasets, there's one(StorageDs) that represents two tables in the db. StorageInput and StorageOutput. Each time an amount of a certain ingredient is bought, a new row is added to the StorageInput table and same thing for StorageOutput when it is sold. Now, in my dataset, there's a sorta aggregate table adapter that takes the ingredients with a certain IngredientIDs from StorageInput, sums the up, takes the same ingredientID from StorageOutput table and subtracts them to yield what is left. Here's the query:

SELECT IngredientID, SUM(INPUTS) AS InputSum, SUM(OUTPUTS) AS OutputSum, SUM(INPUTS) - SUM(OUTPUTS) AS TotalSum
FROM (SELECT StorageInput.Units AS INPUTS, StorageOutput.Units AS OUTPUTS, StorageInput.IngredientID
FROM StorageInput INNER JOIN
StorageOutput ON StorageInput.IngredientID = StorageOutput.IngredientID) AS IOSUM
GROUP BY IngredientID

but something weird happens! For instance when the Sum of StorageInput is supposed to be 50 for a certain ingredient, the first time I execute the query the sum of StorageInput for that ingredient is reported as 100, next time 150 and so on.
Basically, I guess what the query does every time is add the sum of StorageInput to itself one extra time!
It'd be great if anyone could help me with this.
Thanks a lot.

Do you get the same behavior when execute via query analyzer or sql mgmt studio?

A select statement should just the return the data it finds in the database. It does not maintain a running sum just because you run it multiple times. I suggest you double check your client code to make sure your dataset/adapter/variable is handled correctly.

|||oj, thanks for your reply. I forgot to mention that I'm giving SQL EXPRESS a try. So I can't test the query with Query Analyzer or Management Studio for that matter. All I'm able to do is test the query with Query Builder or Preview Data in VS and they both return incorrect values. Considering this, I suppose checking the client code is also out.
|||

You should be able to connect to sqlexpress from QA or Mgmt. If the instance is on the same machine as QA, you can use ".\sqlexpress" as the server name. If it's on a remote node, you will have enable sqlexpress to allow remote connection. Launch Surface Area Conf to change the setting.