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

scruffy yUML diagram of the schema

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

Are you getting an error? What does it say? Your query has an aggregate function (SUM) - how are the records GROUPed BY? The SELECT fields that aren't aggretated need to be in a GROUP BY clause. — Mathieu Guindon
– Mathieu Guindon, Commented May 13, 2017 at 2:30
The Statement that I wrote works fine, the problem is that it only gives me the Total Price. As there's always only one unique invoiceId on the Service table, the Recordset returned contains only one row, that's why i didn't use the GROUP BY... I'm trying to include between 'SELECT' and 'FROM' two more values (Columns), which would be the SUM of Prices which match to the invoiceId AND the type=Painting, and another column to be the TotalPrice (SUM) of the repairs ehich match the invoiceId and the type IS NOT Painting (that is all the other types) — Vinícius Ollitta
– Vinícius Ollitta, Commented May 13, 2017 at 2:37
Is this MySQL or SQL Server? Because the syntax isn't the same on both. — Mathieu Guindon
– Mathieu Guindon, Commented May 13, 2017 at 2:38
I'm using the ODBC object on Excel (VBA), with MySQL connection — Vinícius Ollitta
– Vinícius Ollitta, Commented May 13, 2017 at 2:39
Costumers are people who assemble costumes, i.e. clothes worn by actors - so a small subset of the car buying market. Perhaps you're thinking of customers — Strawberry
– Strawberry, Commented May 13, 2017 at 4:07

Mathieu Guindon · Accepted Answer · 2017-05-13 03:52:08Z

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

Thanks a lot :) I'll fix everything, the schema too. You really helped me a lot!
Hi @MathieuGuidon, sorry i have a last question haha how did you create the tables design which you inserted in the question?

Collectives™ on Stack Overflow

Multiple Filter inside SELECT SQL Statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related