Select the total book value of all vehicles for a customer (SQL)

I have the following database schema (Microsoft SQL Server Compact Edition):

How can I select a customer record or customer records, with an additional field containing the total book value of all vehicles owned by the customer? I tried something like:

SELECT Customers.ID, Customers.FirstName开发者_如何转开发, ..., SUM(Receipts.BookValue) FROM Customers INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID INNER JOIN Receipts ON Receipts.VehicleID = Vehicles.ID;

but was unsuccessful. I'm thinking I might need a GROUP BY or something?

SELECT Customers.ID, SUM(Receipts.BookValue) FROM Customers INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID INNER JOIN Receipts ON Receipts.VehicleID = Vehicles.ID
GROUP BY Customers.ID

Also please note that when using aggregates like Sum you must ensure that all additional fields are part of an aggregate type or in the GROUP BY list.

Sometimes its easier to make 2 queries, the inner query does all the summing and the rest displays additional info like this:

SELECT t.TheSum,
       c.ID,
       c.CompanyName FROM
   ( SELECT Customers.ID as TheID, 
           SUM(Receipts.BookValue) AS TheSum
    FROM Customers INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID INNER JOIN    Receipts ON Receipts.VehicleID = Vehicles.ID
    GROUP BY Customers.ID
) t
INNER JOIN Customers c ON c.ID = t.TheID

Yes, you need a GROU BY <list of every column that is not an aggregate> (SUM, COUNT, etc.)

select c.ID, c.FirstName, c.LastName, bv.BookValueSum
from Customers c
inner join (
    select v.CustomerID, sum(r.BookValue) as BookValueSum
    from Vehicles v
    inner join Receipts r on v.ID = r.VehicleID
    group by v.CustomerID
) bv on c.CustomerID = bv.CustomerID

If you want the book value aggregate and all the customer fields. Something like this will work

SELECT 
  C.*,
  BV.TotalBookValue
FROM Customers C
Inner Join (SELECT 
              Customers.ID as CustomerID, 
              SUM(Receipts.BookValue) AS TotalBookValue
            FROM Customers 
            INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID 
            INNER JOIN Receipts ON Receipts.VehicleID = Vehicles.ID
            GROUP BY Customers.ID) BV 
   ON C.ID = BV.CustomerID