SUM returns incorrect value in SQL Server Mobile Query ... Bug??

Hi all,

I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.

I am running Visual Studio 2005 and have created 2 tables:

Orders and OrderLines which are set up in master detail fashion.

The SQL Statement I create in the Query Builder is as follows:

SELECT     Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM         Orders, OrderLines
WHERE     Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address

Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo

Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile

Here are a couple of screenshots which will demonstrate what I mean.

Here is the contents of the Orders and OrderLines tables.  Each order has only 1 line item in it.

 http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceordersdata.html

When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.

http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceerror1.html

I hope this explains it better.

Anyone have any ideas  

Thanks in advance

... I edited the above post and included screenshots of the issue I am experiencing.

Thanks again.

Has anyone tried to reproduce this problem If it is a bug, it should be pretty easy to reproduce.

Any help would be appreciated.

Thanks Again.

 I cant seem to attached files to this forum ...

Whats your email address and I'll email it to you

Otherwise, do you have a location I can upload it to

Thanks again.

Hi Pragya,

The Orders Table schema is available here:

http://public.fotki.com/AussieNoobie/sqlerrors/orders.html

The OrderLines Table schema is available here:

http://public.fotki.com/AussieNoobie/sqlerrors/orderlines.html

The relationship setup in the VS2005 project is as follows:

http://public.fotki.com/AussieNoobie/sqlerrors/ordersorderlinesrel.html

The Database XSD is available here:

http://public.fotki.com/AussieNoobie/sqlerrors/dbxsd.html

Thanks again for your help.

Hi Pragya,

I have emailed you the SDF file in question.

Thanks again.

I have just been doing some testing and found the following:

If I issue the following statement:

SELECT Orders.OrderID, Orders.OrderNo, Orders.OrderDate, Orders.Priority, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM Orders, OrderLines
WHERE Orders.OrderID = OrderLines.OrderID
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.OrderID

I get the incorrect values:

OrderID, OrderNo, OrderDate, Priority, Total

However, if I select the OrderLines.OrderID instead of the Orders.OrderID, it returns the correct values:

SELECT OrderLines.OrderID, Orders.OrderNo, Orders.OrderDate, Orders.Priority, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM Orders, OrderLines
WHERE Orders.OrderID = OrderLines.OrderID
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, OrderLines.OrderID

OrderID, OrderNo, OrderDate, Priority, Total

This is fixed by creating an Index on the foreign key column of the OrderLines table.

ie. Create an index on OrderLines.OrderID and this issue will go away ...

Now the queries return the correct values and all is good