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 :

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

CodePudding user response：

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.

CodePudding user response：

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;

CodePudding user response：

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: