Problème de groupement

Bonjour a tous, j'ai un souci de requête

Imaginons la requete suivante :

Code:

---

1 2 3

select lib, sum(val) from table group by lib

---

comment faire pour avoir une colonne de plus ayant sum(val) where lib=1 par exemple?
J'aimerais éviter une sous requete si possible (car la mienne est bien plus grosse)

J'ai essayé

Code:

---

1 2 3 4

select t1.lib, sum(t1.val),sum(t2.val) from table t1, table t2 where t2.lib=1 group by t1.lib

---

Mais les données sont fausses

Merci d'avance

C'est la même solution que sur votre autre sujet, mais ce sera probablement plus compréhensible avec cette syntaxe allégée :

Code:

---

1 2 3 4

select lib, sum(val) as val_all, sum(case lib when '1' then val else 0 end) as val_lib_1 from matable group by lib

---

je ne sais si elle fonctionne

Code:

---

1 2 3 4

SELECT lib, sum(tval),sum(decode(lib,1,tval,0)) FROM TABLE GROUP BY lib

---

Citation:

---

Envoyé par Waldar

C'est la même solution que sur votre autre sujet, mais ce sera probablement plus compréhensible avec cette syntaxe allégée :

Code:

---

1 2 3 4

select lib, sum(val) as val_all, sum(case lib when '1' then val else 0 end) as val_lib_1 from matable group by lib

---

---

Non car admettons :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT lib, sum(val) AS val_all, sum(case lib when 'lib1' then val else 0 end) AS val_lib_1   from (select 'lib1' lib, 1 val from dual UNION ALL select 'lib1' lib, 1 val from dual UNION ALL select 'lib1' lib, 1 val from dual UNION ALL select 'lib2' lib, 2 val from dual UNION ALL select 'lib2' lib, 2 val from dual UNION ALL select 'lib3' lib, 3 val from dual UNION ALL select 'lib5' lib, 5 val from dual) group by lib

---

le resultat donne :

LIB VAL_ALL VAL_LIB_1
---- ---------------------- ----------------------
lib1 3 3
lib2 4 0
lib3 3 0
lib5 5 0

Au lieu de :
LIB VAL_ALL VAL_LIB_1
---- ---------------------- ----------------------
lib1 3 3
lib2 4 3
lib3 3 3
lib5 5 3

Citation:

---

Envoyé par teach

je ne sais si elle fonctionne

Code:

---

1 2 3 4

SELECT lib, sum(tval),sum(decode(lib,1,tval,0)) FROM TABLE GROUP BY lib

---

---

Non ca ne passe pas

la sous requête que tu ne veux pas alors

Code:

---

1 2 3 4 5 6 7

SELECT lib, sum(tval),(SELECT lib, sum(tval) FROM TABLE where lib=1) as SUM1   FROM TABLE GROUP BY lib,SUM1

---

Ah vous voulez propager l'information ?

Je réfléchis à une solution analytique, mais pour le moment j'en n'en vois pas.
Vous pouvez faire soit une requête scalaire, soit une sous-requête.
Je préfère la seconde option :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT lib, sum(val) AS val_all, val_lib1 FROM MaTable CROSS JOIN (select sum(val) as val_lib1 from MaTable where lib = 'lib1') GROUP BY lib, val_lib1 ORDER BY LIB ASC;

---

plutôt ceci

Code:

---

1 2 3 4 5 6 7 8

SELECT lib, sum(tval),(SELECT sum(tval) FROM TABLE WHERE lib=1) AS SUM1   FROM TABLE GROUP BY lib,SUM1

---

Ma requete va être horrible...
Elle comporte déjà 3 union, il faudrait refaire 2 sous requêtes par union

J'aurai du y penser plus tôt :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

With MaTable AS ( SELECT 'lib1' as lib, 1 as val FROM dual UNION ALL SELECT 'lib1' , 1 FROM dual UNION ALL SELECT 'lib1' , 1 FROM dual UNION ALL SELECT 'lib2' , 2 FROM dual UNION ALL SELECT 'lib2' , 2 FROM dual UNION ALL SELECT 'lib3' , 3 FROM dual UNION ALL SELECT 'lib5' , 5 FROM dual ) SELECT lib, sum(val) AS val_all, max(case lib when 'lib1' then sum(val) end) over() as val1 FROM MaTable GROUP BY lib ORDER BY LIB ASC;   LIB VAL_ALL VAL1 lib1 3 3 lib2 4 3 lib3 3 3 lib5 5 3

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

with Tab as ( SELECT 'lib1' lib, 1 val FROM dual UNION ALL SELECT 'lib1' lib, 1 val FROM dual UNION ALL SELECT 'lib1' lib, 1 val FROM dual UNION ALL SELECT 'lib2' lib, 2 val FROM dual UNION ALL SELECT 'lib2' lib, 2 val FROM dual UNION ALL SELECT 'lib3' lib, 3 val FROM dual UNION ALL SELECT 'lib5' lib, 5 val FROM dual ) SELECT lib, sum(val), (SELECT lib, sum(val) FROM Tab WHERE lib=1) AS SUM1 FROM Tab GROUP BY lib,SUM1

---

ORA-00904: "SUM1": invalid identifier

Pas mal waldar! que veut dire le over() ?

En partant de ton exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT lib, sum(val) AS val_all, sum(case lib when 'lib1' then sum(val) else 0 end) over () AS val_lib_1 FROM (SELECT 'lib1' lib, 1 val FROM dual UNION ALL SELECT 'lib1' lib, 1 val FROM dual UNION ALL SELECT 'lib1' lib, 1 val FROM dual UNION ALL SELECT 'lib2' lib, 2 val FROM dual UNION ALL SELECT 'lib2' lib, 2 val FROM dual UNION ALL SELECT 'lib3' lib, 3 val FROM dual UNION ALL SELECT 'lib5' lib, 5 val FROM dual) GROUP BY lib

---