How do I replace the columns of duplicate records in a SELECT query?

Question

I am designing a database for an auto repair shop. Each car model has a specific cost for a specific service. I.e. Toyota Camry - Change Oil - $250, Toyota Corolla - Change Oil - $300.

A service can use multiple spare parts. I am trying to write a query that invoices a customer his bill. When only one part is used for a service, the query works correctly. However, when multiple parts are used, the cost of the service is charged multiple times along with the cost of the part. Ideally, any duplicate services (with different parts) should have a cost of $0 so I can correctly invoice the customer.

Here is my SQL code:

SELECT s.name                                                 AS 'Service',
       cost                                                   AS `Cost of Service`,
       p.name                                                 AS `Part Used`,
       quantity                                               AS `Quantity`,
       price_sold                                             AS 'Cost of Part',
       (SELECT Quantity * `Cost of Part` + `Cost of Service`) AS `Total Bill`
FROM work_order
         JOIN client ON client.client_ID = work_order.client_ID
         JOIN vehicle v ON v.vehicle_ID = work_order.vehicle_ID
         JOIN model m ON m.model_ID = v.model_ID
         JOIN completed_work_order cwo ON work_order.work_order_ID = cwo.work_order_ID
         JOIN service s ON s.service_ID = cwo.service_ID
         JOIN model_services ms ON m.model_ID = ms.model_ID AND s.service_ID = ms.service_ID
         JOIN used_parts up ON cwo.completed_work_order_ID = up.completed_work_order_ID
         JOIN part p ON p.part_ID = up.part_ID
WHERE client.client_ID = 6;

Here is the result:

As you can see, the service "Power Steering Check" appears twice which makes sense but the cost of service is also appearing again and so the customer is being incorrectly billed. Is there anyway to replace the cost of these duplicate services (with different spare parts) as $0.00 so the billing will be accurate? Multiple parts that belong to the same service should be able to be added without the customer paying for the service cost all over again.

Bonus: If there is a way to add a custom row at the end that is the sum of the "Total Bill" column, that would be amazing. (But this is a secondary priority).

user_19192899 · Answer

Two alternatives occur to me.

One that concatenates "Part Used" so that there is a single record.
The other is that you make a rank and that if it is the first row, count the Cost of Service and if it is duplicated, do not add it.

 SELECT orders.[Service], 
         orders.[Cost of Service], 
         orders.[Part Used], 
         orders.[Quantity], orders.[Cost of Part], 
  SUM( CASE WHEN Rank = 1 THEN (orders.[Quantity] * orders.[Cost of Part] + orders.[Cost of Service]) ELSE orders.[Cost of Service] END ) [Total Bill] 

FROM ( 
SELECT s.name AS [Service], 
       cost AS [Cost of Service], 
       p.name AS [Part Used], 
       quantity AS Quantity, 
       price_sold AS [Cost of Part], 
       RANK() OVER (PARTITION BY s.name, p.name ORDER BY cost DESC) AS Rank 
FROM work_order JOIN client ON client.client_ID = work_order.client_ID JOIN vehicle v ON v.vehicle_ID = work_order.vehicle_ID JOIN model m ON m.model_ID = v.model_ID JOIN completed_work_order cwo ON work_order.work_order_ID = cwo.work_order_ID JOIN service s ON s.service_ID = cwo.service_ID JOIN model_services ms ON m.model_ID = ms.model_ID AND s.service_ID = ms.service_ID JOIN used_parts up ON cwo.completed_work_order_ID = up.completed_work_order_ID JOIN part p ON p.part_ID = up.part_ID WHERE client.client_ID = 6 
) orders 
GROUP BY orders.[Service], orders.[Cost of Service], orders.[Part Used], orders.[Quantity], orders.[Cost of Part]```

user_13915313 · Answer

Another perspective (assume test is original data-set).

It also includes the final running total to give big total at the end.

If that is not needed then inner sub-query is sufficient.

select t1.*, 
sum(total_amount) over (order by service_name, part_name) final_running_total
from 
(select t.*, 
case when service_name <> ifnull((lead(service_name) over (order by null)), 
'null') 
then amount  + sum(qty * part_cost) over (partition by service_name 
order by part_name)
else 
0 end as total_amount
from test t) t1

DB fiddle here.

user_12283788 · Answer

First of all, some credit goes to marian for even giving me the suggestion about RANK() function.

SELECT orders.Service,
       (CASE
            WHEN `Rank` = 1 THEN (orders.`Cost of Service`)
            ELSE 0 END)                                          AS 'Service Cost',
       orders.`Part Used`,
       orders.Quantity,
       orders.`Cost of Part`,
       SUM(CASE
               WHEN `Rank` = 1 THEN (orders.Quantity * orders.`Cost of Part` + orders.`Cost of Service`)
               ELSE orders.Quantity * orders.`Cost of Part` END) AS `Total Bill`
FROM (SELECT s.name                                                     AS 'Service',
             cost                                                       AS `Cost of Service`,
             p.name                                                     AS `Part Used`,
             quantity                                                   AS `Quantity`,
             price_sold                                                 AS 'Cost of Part',
             RANK() OVER ( PARTITION BY s.name ORDER BY s.name, p.name) AS `Rank`
      FROM work_order
               JOIN client ON client.client_ID = work_order.client_ID
               JOIN vehicle v ON v.vehicle_ID = work_order.vehicle_ID
               JOIN model m ON m.model_ID = v.model_ID
               JOIN completed_work_order cwo ON work_order.work_order_ID = cwo.work_order_ID
               JOIN service s ON s.service_ID = cwo.service_ID
               JOIN model_services ms ON m.model_ID = ms.model_ID AND s.service_ID = ms.service_ID
               JOIN used_parts up ON cwo.completed_work_order_ID = up.completed_work_order_ID
               JOIN part p ON p.part_ID = up.part_ID
      WHERE client.client_ID = 6) AS orders
GROUP BY orders.Service, orders.`Cost of Service`, orders.`Part Used`, orders.Quantity, orders.`Cost of Part`;

The trick is to create a RANK over the duplicate value which in my case in the 'Service'. Then we use CASE to check when the Rank is 1, then everything should work as normal but for higher rank values, set the cost of model services to 0, and only compute the cost of parts only for total bills. And here is the result:

How do I replace the columns of duplicate records in a SELECT query?

3 Answers