Multiple Filter inside SELECT SQL Statement

Question

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

Answer 1

Use CASE WHEN in your SELECT clause, to select the value that's conditional to the type:

SELECT
   ...
   CASE WHEN r.Type = 'Painting' THEN r.Price ELSE 0 END PaintWorkPrice,
   CASE WHEN r.Type <> 'Painting' THEN r.Price ELSE 0 END OtherWorkPrice,
FROM ...

That's one thing.

The other thing is that you're not selecting anything from the Services table, and making your query much more complicated than it needs to be.

If you can modify the schema, remove the ServiceId primary key field, and use Services.InvoiceId as a primary key instead: that will enforce the 1:1 relationship naturally.

FROM Repairs r
INNER JOIN Invoices i ON r.InvoiceId = i.InvoiceId
INNER JOIN Customers c ON i.CustomerId = c.CustomerId

The data you want to aggregate is granular to Repairs, so you select FROM that, and then move your way through the foreign keys up to Customers.

SELECT
    i.CarModel
   ,i.Color
   ,i.Plate
   ,CONCAT(c.FirstName,' ',c.LastName) Name
   ,CASE WHEN r.Type = 'Painting' THEN r.Price ELSE 0 END PaintWorkPrice
   ,CASE WHEN r.Type <> 'Painting' THEN r.Price ELSE 0 END OtherWorkPrice
   ,r.Price
FROM Repairs r
INNER JOIN Invoices i ON r.InvoiceId = i.InvoiceId
INNER JOIN Customers c ON i.CustomerId = c.CustomerId

That's not aggregated yet: there's a record for each repair, for every invoice, under every customer that has an invoice. That part is the sub-query. If you have a parameter, that's where you use it.

WHERE i.InvoiceId = pInvoiceId

If you're just hard-coding an ID, that's where you do it too.

Now type SELECT q.* FROM ( on the line above, and ) q under the WHERE clause, then replace the q.* with the fields you're not aggregating - and aggregate the others. The result should be something like this:

SELECT
     q.CarModel
    ,q.Color
    ,q.Plate
    ,q.Name
    ,SUM(q.PaintWorkPrice) PaintAmount
    ,SUM(q.OtherWorkPrice) OtherAmount
    ,SUM(q.Price) TotalAmount
FROM (
    SELECT
        i.CarModel
       ,i.Color
       ,i.Plate
       ,CONCAT(c.FirstName,' ',c.LastName) Name
       ,CASE WHEN r.Type = 'Painting' THEN r.Price ELSE 0 END PaintWorkPrice
       ,CASE WHEN r.Type <> 'Painting' THEN r.Price ELSE 0 END OtherWorkPrice
       ,r.Price
    FROM Repairs r
    INNER JOIN Invoices i ON r.InvoiceId = i.InvoiceId
    INNER JOIN Customers c ON i.CustomerId = c.CustomerId
    WHERE i.InvoiceId = 15
) q
GROUP BY
     q.CarModel
    ,q.Color
    ,q.Plate
    ,q.Name