Question 113176 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

And... it kinda worked?

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
But, there it does not give me anything

Thank you very much if you can clarify my doubt

Tags:

3

Answers


  1. 0

    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:

    enter image description here

    Thanks to WOUNDEDStevenJones for the DB Fiddle link.

    Login or Signup to reply.
  2. 0

    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;

    enter image description here

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search