MySQL Sum of multiple columns script

Question

sorry but I'm a begginer at mysql and I would need some help as fast as possible. So, I have 2 Tables

ProcedureCatalogue:

idProcedure INT(PRIMARY KEY)
ProcedureName VARCHAR(45)
ProcedureCost DOUBLE
ProcedureTax DOUBLE
TotalCost DOUBLE AS ProcedureTax+ProcedureCost

PatientAppointment:

idAppointment INT(PRIMARY KEY)
idProcedure1 INT 
idProcedure2 INT 
idProcedure3 INT
idProcedure4 INT 
idProcedure5 INT
TotalCost DOUBLE

So, the problem is the following: every patient appointment can have up to 5 medical procedures(for example if only 3 the first 3 idProcedcedure will have the id of the procedures and the rest wil be NULL ). I've tried making the 5 id's in PatientAppointment as foreign keys for idProcedure in ProcedureCatalogue but it doesn't seem to work(I'm using latest version of MySQL Workbench and when I'm trying to select the column for idProcedure none of the columns in ProcedureCatalogue appears) but that's not the real problem because it can be completed with the right info when inserting the data.

What I need to do and I really don't know how is to make a script that makes the TotalCost of PatientAppointment as the sum of the TotalCost of each procedure.

For example if you have 2 procedures(can be up to 5(idProcedure from PatientAppointment coresponding with idProcedure from ProcedureCatalog))

you'll have something like: ProcedureCatalog:

   id ProcedureName  ProcedureCost  ProcedureTax  TotalCost
    1 procedure1          20            30           50
    2 procedure2          40            60          100
    3 procedure3          1              1            2
    4 procedure4          5              5           10
    5 procedure5         25             30           55

and for PatientAppointment:

idAppointment      id1   id2   id3  id4  id5  TotalCost  
      1             1     3    NULL NULL NULL     52 
      2             2     4     5   NULL NULL     165
      3             4    NULL  NULL NULL NULL     10
      4             1     2     3    4    5       217

So, please help me with the script for populating the TotalCost in PatientAppointment, I'm trying to make it for the past 6 hours and I had no luck with it :(.

What if I want a sixth procedure, and money is rarely DOUBLE; it's why DECIMAL was invented. — Strawberry, Nov 23 '20 at 07:10
@Strawberry This is part of a bigger University assignment and I'm using the DataTypes that were provided to me. — Tudor Ienesoi, Nov 23 '20 at 07:13
Well, all I can say is, if it was me, I'd start by fixing the schema, to use appropriate data types and a normalised design. — Strawberry, Nov 23 '20 at 07:21
@Strawberry can you please give me some tips for that(as I said I'm a begginer in MySql) — Tudor Ienesoi, Nov 23 '20 at 07:24
Any time you find yourself with enumerated column names (above, say, 2), you can be sure that your design is sub-optimal. The appointment, and the details of the procedures due to take place at that appointment, are two different things and belong in two separate tables. — Strawberry, Nov 23 '20 at 07:31
@Strawberry I see your point and I think I have an idea. I'll get back back if it's working. Thanks! — Tudor Ienesoi, Nov 23 '20 at 07:39
Simply sumarize separate ProcedureCatalogue.TotalCost values wrapping them with COALESCE. — Akina, Nov 23 '20 at 07:45

Akina · Answer 1 · 2020-11-23T08:24:41.337

UPDATE PatientAppointment pa
LEFT JOIN ProcedureCatalogue pc1 ON pa.idProcedure1 = pc1.idProcedure
LEFT JOIN ProcedureCatalogue pc2 ON pa.idProcedure2 = pc2.idProcedure
LEFT JOIN ProcedureCatalogue pc3 ON pa.idProcedure3 = pc3.idProcedure
LEFT JOIN ProcedureCatalogue pc4 ON pa.idProcedure4 = pc4.idProcedure
LEFT JOIN ProcedureCatalogue pc5 ON pa.idProcedure5 = pc5.idProcedure
SET pa.TotalCost =   COALESCE(pc1.TotalCost, 0)
                   + COALESCE(pc2.TotalCost, 0)
                   + COALESCE(pc3.TotalCost, 0)
                   + COALESCE(pc4.TotalCost, 0)
                   + COALESCE(pc5.TotalCost, 0);

fiddle

MySQL Sum of multiple columns script

1 Answers1