Peter Haworth <p...@mollysrevenge.com>
wrote: 
> 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

select Customers.CustID,
    (select sum(value) from Sales where Sales.CustID=Customers.CustID),
    (select sum(value) from Transactions join Expenses on 
(Expenses.TranID=Transactions.TranID)
     where Transactions.CustID=Customers.CustID)
from Customers;

Igor Tandetnik

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to