Check that the milestones of each project have been completed between the start and end dates of the project

Question

Good morning! I'm a bit stuck with this question. I know it's not complicated (Because I'm pretty sure I'm close to the answer) But I've been Stuck for a bit now. I'm totally new to MySQL

My database code : https://srcb.in/OtFsESDjx5

I need to Give the total number of hours worked and the total amount earned per employee in all projects, including the employee's name and email address. (Expected result: idemploye, nomEmploye, adresseCourriel, nbrHeures, somme gagnée)

So i tried this query :

SELECT r.idEmploye, e.nomEmploye, e.adresseCourriel, r.nbrHeures, r.somme_gagnée
     FROM  (SELECT idEmploye,
                         sum(nbrHeure) AS nbrHeures,
                         sum(nbrHeure*PrixHeure) AS somme_gagnée
                   FROM  ressourcesprojet_
               GROUP BY  idEmploye
           ) AS r

INNER JOIN  employe_  AS e;

It gives me back to many columns, like it's repeating idemployee for every nomEmploye

So i tried to remove the "Group By", because I thought that by grouping it I would be repeating it several times internally :

SELECT r.idEmploye, e.nomEmploye, e.adresseCourriel, r.nbrHeures, r.somme_gagnée
      FROM  (SELECT idEmploye,
                          sum(nbrHeure) AS nbrHeures,
                          sum(nbrHeure*PrixHeure) AS somme_gagnée
                    FROM  ressourcesprojet_
            ) AS r
 
JOIN  employe_  AS e;

And... it kinda worked? Now, effectively, I only have 4 employees but everything that is r.idEmploye, r.nbrHeures, r.somme_gagnée it's "repeated". As if it just took the first one and repeated it multiple times for .r

As a last test, I said that possibly the best thing would be to create an INNER JOIN between e.nomEmploye = r.idEmploye But I'm not sure that's it because it just doesn't select anything for me

SELECT r.idEmploye, e.nomEmploye, e.adresseCourriel, r.nbrHeures, r.somme_gagnée
      FROM  (SELECT idEmploye,
                          sum(nbrHeure) AS nbrHeures,
                          sum(nbrHeure*PrixHeure) AS somme_gagnée
                    FROM  ressourcesprojet_
                GROUP BY  idEmploye
            ) AS r
 
INNER JOIN employe_  AS e
    ON e.nomEmploye = r.idEmploye;

But, there it does not give me anything

Thank you very much if you can clarify my doubt

Answer 1

How about this :

SELECT e.idEmploye, e.nomEmploye, e.adresseCourriel, inner_q.nbrHeures, inner_q.somme_gagnee
FROM
( SELECT idEmploye, sum(nbrHeure) AS nbrHeures, sum(nbrHeure*PrixHeure) AS somme_gagnee
FROM  RessourcesProjet_ GROUP BY  idEmploye ) as inner_q     
JOIN Employe_ e
WHERE e.idEmploye = inner_q.idEmploye

Seems like what you wanted:

Thanks to WOUNDEDStevenJones for the DB Fiddle link.

Answer 2

You will need to join by the criteria that the id is matching:

select RessourcesProjet_.idEmploye, sum(nbrHeure) AS nbrHeures, sum(nbrHeure*PrixHeure) AS somme_gagnee
from RessourcesProjet_
join Employe_
on RessourcesProjet_.idEmploye = Employe_.idEmploye
group by RessourcesProjet_.idEmploye;

Answer 3

I think you can just do a JOIN with your subquery (still using the GROUP BY) and Employe_ tables:

SELECT
    r.idEmploye,
    e.nomEmploye,
    e.adresseCourriel,
    r.nbrHeures,
    r.somme_gagnee
FROM (
    SELECT
        idEmploye,
        SUM(nbrHeure) AS nbrHeures,
        SUM(nbrHeure*PrixHeure) AS somme_gagnee
    FROM
        RessourcesProjet_
    GROUP BY
        idEmploye
) AS r
JOIN Employe_ e ON e.idEmploye = r.idEmploye

Live example at https://www.db-fiddle.com/f/hkYtcPTF4KoYEuXd1DKhkB/2 returns:

idEmploye	nbrHeures	somme_gagnee	nomEmploye	adresseCourriel
1212	3000	105000	Marie St-Jerome	marie.stjerome@ca
1876	2500	102500	Martin Rey	martin.rey@ca
2231	750	46250	Jean Pierre Bordeau	jean.bordeau@ca
4354	2000	62000	Louise Gagnon	louise.gagnon@ca