Is there anyway to do SUM on join table?

I would like to start using SQL Server CE rather than SQLite for an application I am developing, due to it's replication support for SQL Server however there appears to be a gaping hole in it that means I can't even consider it. Specifically to do with aggregates against detail tables.

Is there any way to do the equivalent of the following in SQL using SQL Server CE?

SELECT [Customer_Id], [Customer_name], COALESCE([OT].[Count_Orders],0) [CountOrders], COALESCE([OT].[Sum_Value],0) [OrderValue] FROM [Customers] LEFT OUTER JOIN (SELECT .[Customer_Id], COUNT(*) [Count_Orders], SUM(.[Order_Value]) [Sum_Value] FROM [Orders] GROUP BY .[Customer_Id]) [OT] ON [OT].[Customer_Id] = [Customers].[Customer_Id]);

Any help would be most welcome

Adrian Heath

I think the short answer is no. The long answer is no, not in a single statement. One of the differences when you are working with such a lightweight database is that you need to adjust the way you do operations. Typically if you are writing a client application that uses sql server ce you can split the data processing load between the database (ie selecting records) and the application itself.

In the example you have provided you would have to do the aggregation by Customer_Id across the Orders table and then in memory do the join across to Customer information..

Alternatively you could just maintain an additional table with this information already aggregated - again because it is a client application and you are not expecting thousands of concurrent updates this becomes a workable solution.

|||

This works fine (from Northwind sample database) (Ship Name = Customer Name)

SELECT Orders.[Ship Name], SUM([Order Details].[Unit Price]) AS Total
FROM [Order Details] INNER JOIN
Orders ON [Order Details].[Order ID] = Orders.[Order ID]
GROUP BY Orders.[Ship Name]

Hope this assists.