Peter Haworth <[email protected]>
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
