MySql complex query - SUM on multiple and variable columns

I have the following table structure (simplified version)

+----------------+  +-----------------+  +------+
| fee_definition |  | user_fee        |  | user |
+----------------+  +-----------------+  +------+
| id             |  | user_id         |  | id   |
| label          |  | fee_id          |  | ...  |
| case1          |  | case            |  +------+
| case2          |  | manual_override |
| case3          |  +-----------------+
| case4          |
| case5          |  
+----------------+

Base on a pretty simple algorithm id determine which case fits the user to determine the amount of money they have to pay. A user_fee can be base on 1 to no limit number of fees definitions. which mean i can have the following content in the intersection table

+-----------+----------+--------+-------------------+
|  user_id  |  fee_id  |  case  |  manual_override  |
+-----------+----------+--------+-------------------+
|  1        |  1       |  case1 |                   |
|  1        |  3       |  case1 |                   |
|  1        |  5       |  case1 |  50.22            |
|  2        |  1       |  case5 |                   |
|  3        |  1       |  case2 |                   |
|  3        |  2       |  case2 |  18.50            |
+-----------+----------+--------+-------------------+

If a user is setted to have the case 1, all the fees listed under the case 1 where the value is different from 0 get picked. Same goes for the four other cases.

Just for reference on how i did things here is the actual query that I execute which is written in french (sorry for that but since we are a team of french speaking developpers, we mostly write in our code and queries in french).:

SELECT 
    `etudiant_etu`.*,
    `session_etudiant_set`.*,
    SUM(ROUND(frais_session_etudiant.fse_frais_manuel*100)/100) AS `fse_frais_manuel`,  
    `frais_session_etudiant`.`des_colonne`,           
    SUM(ROUND(definition_frais_des.des_quebecCanada*100)/100) AS `des_quebecCanada`,         
    SUM(ROUND(definition_frais_des.des_etranger*100)/100) AS `des_etranger`, 
    SUM(ROUND(definition_frais_des.des_non_credite*100)/100) AS `des_non_credite`, 
    SUM(ROUND(definition_frais_des.des_visiteur*100)/100) AS `des_visiteur`, 
    SUM(ROUND(definition_frais_des.des_explore*100)/100) AS `des_explore`, 
    `type_etudiant_tye`.*,
    `type_formation_tyf`.*,
    `pays_pys`.*,
    `province_prc`.*
FROM `etudiant_etu`
INNER JOIN `session_etudiant_set` 
    ON session_etudiant_set.etu_id = etudiant_etu.etu_id
INNER JOIN `frais_session_etudiant` 
    ON frais_session_etudiant.set_id = session_etudiant_set.set_id
INNER JOIN `definition_frais_des` 
    ON definition_frais_des.des_id = frais_session_etudiant.des_id
LEFT JOIN `type_etudiant_tye` 
    ON type_etudiant_tye.tye_id = session_etudiant_set.tye_id
LEFT JOIN `type_formation_tyf` 
    ON type_formation_tyf.tyf_id = session_etudiant_set.tyf_id
LEFT JOIN `pays_pys` 
    ON pays_pys.pys_code = etudiant_etu.pys_adresse_permanente_code
LEFT JOIN `province_prc` 
    ON province_prc.prc_code = etudiant_etu.prc_adresse_permanente_code 
WHERE (set_session = 'P11') 
GROUP BY `session_etudiant_set`.`set_id` 
ORDER BY `etu_nom` asc, `etu_prenom` ASC

as for reference from the actual query with the simplified version:

simplified version         actual version
fee_definition.id          definition_frais_des.des_id
fee_definition.case1       definition_frais_des.des_quebecCanada
fee_definition.case2       definition_frais_des.des_etranger
fee_definition.case3       definition_frais_des.des_non_credite
fee_definition.case4       definition_frais_des.des_visiteur
fee_definition.case5       definition_frais_des.des_explore

user_fee.user_id           frais_session_etudiant.set_id
user_fee.fee_id            frais_session_etudiant.des_id
user_fee.case              frais_session_etudiant.des_colonne
user_fee.manual_override   frais_session_etudiant.fes_frais_manuel

user.id                    session_etudiant_set.set_id

The problem I have is when it comes to handling the manual override setting. What would be the best way of doing this?

I would rather this to be handled in the query itself than in the programmation.

the logic behind what I am looking for goes as follow

get the SUM of the fees to be charged for a user and开发者_如何学Go if an override value as been set, use that value instead of the actual value setted in the fee_definition, else use the value in the fee_definition.

I don't mind to loose the 4 not used cases and only keep the right column

Edited to display final result

This is the query I ended with, five levels of IF's

'IF(`frais_session_etudiant`.des_colonne= "des_quebec_canada",
    SUM(IF(`frais_session_etudiant`.fse_frais_manuel > 0,
        ROUND(`frais_session_etudiant`.fse_frais_manuel*100)/100,
        ROUND(definition_frais_des.des_quebec_canada*100)/100)
    ),
    IF(`frais_session_etudiant`.des_colonne= "des_etranger",
        SUM(IF(`frais_session_etudiant`.fse_frais_manuel > 0,
            ROUND(`frais_session_etudiant`.fse_frais_manuel*100)/100,
            ROUND(definition_frais_des.des_etranger*100)/100)
        ),
        IF(`frais_session_etudiant`.des_colonne= "des_non_credite",
            SUM(IF(`frais_session_etudiant`.fse_frais_manuel > 0,
                ROUND(`frais_session_etudiant`.fse_frais_manuel*100)/100,
                ROUND(definition_frais_des.des_non_credite*100)/100)
            ),
            IF(`frais_session_etudiant`.des_colonne= "des_visiteur",
                SUM(IF(`frais_session_etudiant`.fse_frais_manuel > 0,
                    ROUND(`frais_session_etudiant`.fse_frais_manuel*100)/100,
                    ROUND(definition_frais_des.des_visiteur*100)/100)
                ),
                IF(`frais_session_etudiant`.des_colonne= "des_explore",
                    SUM(IF(`frais_session_etudiant`.fse_frais_manuel > 0,
                    ROUND(`frais_session_etudiant`.fse_frais_manuel*100)/100,
                    ROUND(definition_frais_des.des_explore*100)/100)
                ),
                    0
                )
            )
        )
    )
) as frais'

That's a monster! as said by Ted Hopp :D

You can use IFNULL(manual_override,non-override-value)