CODEWITHSUNDEEP
sqlsql-serversql-server-ce
Jake Petroules
Jake Petroules

Reputation: 24190

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

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

alt text

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?

Upvotes: 0

Views: 180

Answers (4)

John Hartsock
John Hartsock

Reputation: 86892

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

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171529

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

Upvotes: 0

Paulo Scardine
Paulo Scardine

Reputation: 77359

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

Upvotes: 0

JonH
JonH

Reputation: 33173

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

Upvotes: 3

Related Questions