Bonjour,
J'ai une table d'employés avec leur manager, je dois trouver la liste des emplois don le salaire est supérieur au salaire moyen des managers

La requête ci-dessous me donne le salaire moyen des managers

SELECT avg(sal) FROM emp  WHERE empno IN(SELECT DISTINCT mgr FROM emp)

La requête ci-dessous me donne le salaire moyen en fonction des emplois

SELECT job, avg(sal) AS salaire_moyen FROM emp GROUP BY job

Quand je regroupe les 2 requêtes cela ne fonctionne pas

SELECT job, avg(sal) AS salaire_moyen FROM emp GROUP BY job WHERE salaire_moyen < (SELECT avg(sal) FROM emp  WHERE empno IN(SELECT DISTINCT mgr FROM emp))

Merci pour votre aide
Yann

Salut

C'est possible d'avoir un schéma de ta base de données. Pour voir si il y a des jointures entre tes tables.

Pour cette exercice il y a seulement une table

La base

CREATE TABLE emp (
  empno smallint(6) NOT NULL default '0',
  ename varchar(20) NOT NULL default '',
  job varchar(30) NOT NULL default '',
  mgr smallint(6) default NULL,
  hiredate date NOT NULL default '0000-00-00',
  sal smallint(6) NOT NULL default '0',
  comm smallint(6) default NULL,
  deptno smallint(6) NOT NULL default '0',
  PRIMARY KEY  (empno),
  UNIQUE KEY empno (empno)
) TYPE

Quelques ligne

INSERT INTO emp VALUES (7369, 'smith', 'clerk', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'ward', 'salesman', 7698, '1981-02-21', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'jones', 'manager', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'martin', 'salesman', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'blake', 'manager', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'clark', 'manager', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'scott', 'analyst', 7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'king', 'president', NULL, '1981-11-17', 5000, NULL, 10);

La colonne empno c'est le numéro de l'employé, la colonne mgr correspond au numéro du manager

Tu as déjà une colonne qui donne le poste de ton employé, donc je ne comprends pas ta première requête. Celle qui donnerait le salaire moyen de tes managers, ce serait :

SELECT AVG(sal)
FROM emp
WHERE job = 'manager';

Mais ton énoncé me semble pas clair du tout. Tu veux trouver la liste des emplois qui ont un salaire supérieur à celui des managers... Sauf qu'il y a deux salesmen. On prend lequel ? Tu veux la moyenne des emplois ?
Dans ce cas-ci, il faut faire cela :

--
  SELECT job
    FROM emp
GROUP BY job
  HAVING AVG(sal) > (SELECT AVG(sal)
                     FROM emp
                     WHERE job = 'manager');

L'énoncé

Liste alphabétique des emplois (job, salaire moyen) dont le salaire moyen est supérieur au salaire moyen des Managers.

L'explication de la table

Signification du nom des colonnes
EMPNO ->Numéro d'Employé
ENAME ->Nom Employé
JOB -> Emploi
HIREDATE-> Date Embauche
SAL -> Salaire
COMM -> Commission
MGR -> Numéro du Responsable Hiérarchique


Par exemple l'employé numéro 7499 à comme manager l'employé 7698
il y a que le président qui n'a pas de managers

Yann

Donc en fait, le président est aussi un manager ? Ça me paraît étrange. Ma requête précédente correspond exactement à l'énoncé (trouver les postes dont le salaire moyen est supérieur à celui des managers, il manquerait seulement le ORDER BY).

Sauf que l'exemple semble contredire l'énoncé. En quoi le fait que le président n'a pas de manager est important ?

En fait il ne faut pas prendre seulement les personnes don le job est manager, il faut prendre toutes les personnes qui ont des personnes sous leur responsabilité.
Donc si le numéro de l'employé est dans la colonne MGR c'est que cet employé a d'autre employé sous sa responsabilité

C'est bon j'ai trouvé

SELECT job,AVG(SAL) FROM emp
 GROUP BY job  HAVING AVG(sal) > (SELECT avg(sal)
 FROM emp  WHERE empno IN(SELECT DISTINCT mgr FROM emp));

Donc l'énoncé est faux, tout simplement. Ça complexifie un peu les choses, mais allons-y étape par étape encore une fois. Ta requête pour trouver le salaire moyen s'avère maintenant bonne.

--
SELECT AVG(sal)
  FROM emp
 WHERE empno IN (SELECT DISTINCT mgr
                 FROM emp);

Le problème, c'est ta troisième. On ne peut pas utiliser WHERE comme tu l'as fait, parce que WHERE s'utilise sur des colonnes déjà calculées. Il faut utiliser HAVING :

--
  SELECT job
    FROM emp
GROUP BY job
  HAVING AVG(sal) > (SELECT AVG(sal)
                     FROM emp
                     WHERE empno IN (SELECT DISTINCT mgr
                                     FROM emp))
ORDER BY job ASC;

Merci pour ton aide, désolé pour l'énoncé il n'est pas de moi
Yann