Multiple filters inside SELECT SQL statement
I am writing a SQL query to get some values in a recordset which I will use to wrap the result to TextBoxes on a form in Excel. Component tables:
Customers -> CustomerId, FirstName, LastName, TelNumber
Invoice -> InvoiceId, CustomerId, CarModel, CarColor, CarPlate
Repair → RepairId, InvoiceId, TypeOfRepair, PartOfCar, price
Services → ServiceId, InvoiceId, Date, Status
When a customer arrives at the garage, an invoice is generated associated with that customer. There may be many repairs on the invoice. The client leaves without car repair, but there is an invoice. If the customer decides to have the vehicle repaired, a Service is created that starts with the status "Working on it ...". When the service is completed, the status will change to "Waiting for verification ..."
I want to use a SQL statement to get the following values (columns) for a specific InvoiceId:
CarModel, Color, Plate, CustomerName (FirstName LastName), PaintingTotalValue (where "Painting" is one type in the "Type" column), OtherTotalValue (total cost of all other repairs in this invoice), total cost (total cost, that is painting + others).
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
source to share
Use CASE WHEN
in your sentence SELECT
to select a value that matches a condition:
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 ...
This is one thing.
Another thing is that you do not select anything from the table Services
and make your query much more complicated than it should be.
If you can change the schema, remove the primary key field
ServiceId
and use itServices.InvoiceId
as the primary key instead : this will result in a natural 1: 1 relationship.
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 copy is in dimension to Repairs
, so you select FROM
and then step through foreign keys 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
This is not aggregated yet: there is a record for every repair, for every invoice, every customer who has an invoice. This part is a subquery. If you have a parameter then where do you use it.
WHERE i.InvoiceId = pInvoiceId
If you're just hard-coding the id, that's where you do it.
Now enter SELECT q.* FROM (
in the line above and ) q
in the sentence WHERE
, then replace q.*
with fields that you are not aggregating and aggregate the rest. 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
source to share