I'm writing a SQL Statement to get some values in a Recordset, which I'll use to transfer the result into TextBoxes on a Form in Excel. The tables involved are:
Customers -> CustomerId, FirstName, LastName, TelNumber
Invoice -> InvoiceId, CustomerId, CarModel, CarColor, CarPlate
Repairs -> RepairId, InvoiceId, TypeOfRepair, PartOfCar, Price
Services -> ServiceId, InvoiceId, Date, Status
When a Customer comes to the Garage, an Invoice is created, which is associated with this customer. An invoice can have many Repairs. The customer goes away without repairing the car, but the invoice is there. If the customer decides to repair the car, then a Service is created, which starts with the Status "working on it...". When the service is done, the status change to "Waiting for Check Out..."
I want to use a SQL Statement to retrieve the following Values (columns) for a specific InvoiceId:
CarModel, Color, Plate, CustomerName (FirstName LastName), PaintingTotalValue (where 'Painting' is one type in the column 'Type'), OtherTotalValue (total price of all the other types of repairs in this invoice), total price (total price, that is, painting + other ones).
I wrote the following, to get the values, but I don't know how to get the PaintingTotalValue and OtherTotalVAlue.
SELECT i.CarModel, i.Color, i.Plate, CONCAT(c.FirstName,' ',c.LastName) AS Name, FORMAT(SUM(r.Price),2) AS TotalPrice
FROM Services AS s INNER JOIN Invoices AS i ON s.invoiceId=i.invoiceId
INNER JOIN Repairs AS r ON s.invoiceId=r.invoiceId
INNER JOIN Customers AS c ON i.customerId=c.customerId
WHERE s.invoiceId = 15