Trying to implement the following situation involving 4 tables Customers is the "master table" in that the results should end up with one row for each primary key value in it.
I need to sum the values of a column in the Sales table, which has a column that joins to the primary key of Customers and can have multiple rows for each Customers primary key value. I also need to sum the values of a column in the Expenses table. In order to get the Expenses entries for each primary key value in TableA, I first have to join to the Transactions table using the primary key of Table A, and then link to Expenses using the primary key value of Transactions. Transactions can have multiple rows for each Customers primaryKey value and Expenses can have multiple rows for each Transactions primary key value. Here's the latest attempt. SELECT Customers.CustID,sum(Sales.value) AS totalsales, Sum(Expenses.value) AS totalexpenses FROM Customers LEFT JOIN Sales ON Sales.CustID=Customers.CustID LEFT JOIN Transactions ON Transactions.CustID=Customers.CustID LEFT JOIN Expenses ON Expenses.TranID=Transactions.TranID GROUP BY Customers.CustID What I want to end up with is 1row for each CustID value containing CustID, totalsales, and totalexpenses. I do get one row per CustID but totalsales and totalexpenses are almost always incorrect For example, if there are 10 rows in TableB for a particular value of CustID, the sum of the TableB entries is 10 times what it should be. Any ideas? Pete _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
