group by et max

Bonjour,

Celà fait plusieurs fois que je tombe sur un même problème en SQL et j'ai jamais compris pourquoi ?

Prenons une table Employee avec
emp_id PK
salary
dept_id FK

dept_id FK => est la cle étrangère issue de la table Department.

Si on me demande de trouver le total des salaires pour chaque département

Je peux faire

Code:

---

1 2 3 4

SELECT dept_id, SUM(salary) FROM employee GROUP BY dept_id

---

Maintenant on me demande de trouver le departement qui a le total des salaires le plus élevé.

Je fais donc

Code:

---

1 2 3 4

SELECT MAX(SUM(salary)) FROM employee GROUP BY dept_id

---

Et j'obtiens donc le résultat suivant
MAX(SUM(salary))
12345679

Mais moi je voudrais aussi obtenir la colonne dept_id qui me permettra d'afficher à quel departement correspond ce max.

donc j'essaie de faire:

Code:

---

1 2 3 4

SELECT dept_id, MAX(SUM(salary)) FROM employee GROUP BY dept_id

---

et là j'ai la fameuse erreur
NOT A SINGLE GROUP FUNCTION

Et j'arrive pas à comprendre pourquoi:(

il faut utiliser HAVING dans la clause WHERE ;)

Essayez ceci :

Code:

---

1 2 3 4

SELECT MAX(dept_id) KEEP(DENSE_RANK FIRST ORDER BY SUM(salary) DESC) as dept_id, MAX(SUM(salary)) as sum_salary FROM employee GROUP BY dept_id

---

Ceci doit marcher aussi :

Code:

---

1 2 3 4 5 6

select * from (SELECT dept_id, SUM(salary) FROM employee GROUP BY dept_id order by 2 desc ) where rownum = 1

---

Il y a toujours un piège dans les requêtes de type TOP-N: et si deux enregistrements donnent la même valeur ?

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

mni@DIANA> insert into emp values(1000,'TEST', 'TEST', NULL, sysdate, 10875, null, 40) 2 /   1 ligne crÚÚe.   mni@DIANA> select deptno, sum(sal) 2 from emp 3 group by deptno 4 /   DEPTNO SUM(SAL) ---------- ---------- 30 9400 20 10875 40 10875 10 8750   mni@DIANA> SELECT MAX(deptno) KEEP(DENSE_RANK FIRST ORDER BY SUM(sal) DESC) AS deptno, 2 MAX(SUM(sal)) AS sum_salary 3 FROM emp 4 GROUP BY deptno 5 /   DEPTNO SUM_SALARY ---------- ---------- 40 10875   mni@DIANA> SELECT * FROM 2 (SELECT deptno, SUM(sal) 3 FROM emp 4 GROUP BY deptno 5 ORDER BY 2 DESC 6 ) WHERE rownum = 1 7 /   DEPTNO SUM(SAL) ---------- ---------- 20 10875   mni@DIANA> Select deptno, totsal 2 From ( 3 select deptno, sum(sal) totsal, rank() over (order by sum(sal) desc) rn 4 from emp 5 group by deptno 6 ) 7 Where rn = 1 8 /   DEPTNO TOTSAL ---------- ---------- 20 10875 40 10875   mni@DIANA> select deptno, sum(sal) totsal 2 from emp 3 group by deptno 4 having sum(sal) = (Select Max(tot_sal) 5 From 6 (Select Sum(sal) tot_sal 7 from emp 8 group by deptno) 9 ) 10 /   DEPTNO TOTSAL ---------- ---------- 20 10875 40 10875

---

Citation:

---

Envoyé par nuke_y

Ceci doit marcher aussi :

Code:

---

1 2 3 4 5 6

select * from (SELECT dept_id, SUM(salary) FROM employee GROUP BY dept_id order by 2 desc ) where rownum = 1

---

---

Citation:

---

Envoyé par Waldar

Essayez ceci :

Code:

---

1 2 3 4

SELECT MAX(dept_id) KEEP(DENSE_RANK FIRST ORDER BY SUM(salary) DESC) as dept_id, MAX(SUM(salary)) as sum_salary FROM employee GROUP BY dept_id

---

---

Bonjour,

Les 2 méthodes fonctionnent, par contre je comprends pas pourquoi la requête que j'avais fais renvoie une erreur :?

Citation:

---

Envoyé par orafrance

il faut utiliser HAVING dans la clause WHERE ;)

---

Désolé mais je n'ai pas compris.

Je pense qu'il parlait de la quatrième requête proposée par Mnitu.

Faites très attention à sa remarque, c'est important.

Citation:

---

Envoyé par mnitu

mni@DIANA> Select deptno, totsal
2 From (
3 select deptno, sum(sal) totsal, rank() over (order by sum(sal) desc) rn
4 from emp
5 group by deptno
6 )
7 Where rn = 1
8 /

DEPTNO TOTSAL
---------- ----------
20 10875
40 10875

mni@DIANA> select deptno, sum(sal) totsal
2 from emp
3 group by deptno
4 having sum(sal) = (Select Max(tot_sal)
5 From
6 (Select Sum(sal) tot_sal
7 from emp
8 group by deptno)
9 )
10 /

DEPTNO TOTSAL
---------- ----------
20 10875
40 10875
[/code]

---

Franchement bien vu, faudra que je fasse gaffe quand j'écris une requête de la tourner 7 fois dans ma tête.

Merci.


Par contre je sais pas si c'est plus simple réécrit comme ça ?

Code:

---

1 2 3 4 5 6 7

SELECT dept_id, SUM(salary) FROM employee GROUP BY dept_id HAVING SUM(salary) = (SELECT MAX(SUM(salary)) FROM employee GROUP BY dept_id)

---

Oui c'est un peu plus compact, mais peu de SGBD accepte les agrégats imbriqués.

Oui bonne remarque de mnitu.

Tout dépend du résultat attendu : si c'est 1 seule ligne (parce que le résultat va être affiché dans un champ mono valeur d'un écran par exemple) alors il faudra bien choisir une règle pour ne prendre qu'un seul departement sur les n qui ont la valeur max. Si par contre c'est à but d'analyse alors effectivement il est important de savoir que n departements ont la valeur max.

Citation:

---

Envoyé par Waldar

Oui c'est un peu plus compact, mais peu de SGBD accepte les agrégats imbriqués.

---

Je fais plutôt du Java EE et je suis pas un pro de l'SQL, pourtant j'ai l'impression que parler de standard comme en Java pour SQL c'est plutôt une utopie. Enfin je me trompe peut être mais j'ai l'impression qu'il vaut mieux utiliser toutes les fonction que peux offrir un SGBD vu que de toute les façons si jamais on en change il faudra réécrire une grosse partie des requêtes.

En tout cas merci pour les réponses.

Citation:

---

Envoyé par exhortae

Je fais plutôt du Java EE et je suis pas un pro de l'SQL, pourtant j'ai l'impression que parler de standard comme en Java pour SQL c'est plutôt une utopie. Enfin je me trompe peut être mais j'ai l'impression qu'il vaut mieux utiliser toutes les fonction que peux offrir un SGBD vu que de toute les façons si jamais on en change il faudra réécrire une grosse partie des requêtes.

En tout cas merci pour les réponses.

---

Vous est arrivé à la bon conclusion :lol:
Le problème n’est pas du tout le SQL qui est plus ou moins standardisée. Le vrai problème ce sont les mécanismes de contrôle de l’accès concurrentiel c’est à dire les verrous, les divers choix qui impact profondément les performances, etc. qui ne sont pas du tout standardisés. Oui, d’accord ce n’est pas pareil avec Java mais Java non plus n’est pas un SGBD :lol:.