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

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).

CodePudding user response：

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