LEFT JOIN et SUM

Bonjour,

J'ai une requête qui me donne des lignes correspondant à un critère :

Code:

---

1 2 3 4 5 6 7

id critere val ligne_ref 1 28.7 2 456 0.2 1 3 456 0.1 1 4 11.8 5 457 0.2 4

---

je fais :

Code:

---

1 2	SELECT val FROM table WHERE critere<>''

---

J'obtiens bien mes 3 lignes avec 0.2 0.1 et 0.2.
Maintenant, je voudrais ajouter une autre colonne dans mon select mais sans ajouter de ligne, et dans cette colonne je voudrais avoir 30 : 28.7 + 0.2 + 0.1.
Le champ ligne_ref est celui qui rattache les 2 lignes avec critère à la première ligne.
J'ai essayé des left join avec un SUM, des right join ou inner join, mais cela me modifie le nombre de ligne du résultat.

Code:

---

1 2 3 4 5

SELECT A.val, B.SUM(val) FROM table AS A left join (SELECT SUM(val) FROM table) AS B ON ... ici je ne vois pas WHERE critere<>''

---

Je voudrais :
0.2 30
0.1 30
0.2 12

Merci

En fait, le plus compliqué c'est de renseigner soit critere soit ligne_ref ;)

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

WITH tmp AS ( SELECT DISTINCT val.id , ref.critere AS critere , val.val AS val , val.id AS ligne_ref FROM matable AS val LEFT JOIN matable AS ref ON val.id = ref.ligne_ref WHERE val.critere = '' AND ref.critere <> '' UNION ALL SELECT id , critere , val , ligne_ref FROM matable WHERE critere <> '' ) SELECT id , critere , val , ligne_ref , SUM(val) OVER (PARTITION BY critere) FROM tmp

---

Bonjour,

N'avez-vous pas fait une erreur dans le résulta attendu, n'est-ce pas 29 plutôt que 30 pour les deux premières lignes ?

Dans ce cas :

Code:

---

1 2 3 4 5 6 7 8

WITH Tmp AS ( SELECT id, critere, val, SUM(val) OVER(PARTITION BY COALESCE(ligne_ref, id)) AS somme FROM MaTable ) SELECT val, somme FROM Tmp WHERE critere <> ''

---

Bonjour,

Merci de vos réponses.
Oui, bien sûr c'est 29 et non 30. Vraiment désolé, je vais retourner en maternelle !
J'essayais de comprendre la requête de al1 sachant qu'il n'y avait pas le bon nombre de lignes (avec le select seulement : WHERE val.critere = ''
AND ref.critere <> '' : un des 2 est en trop)

Sinon, je ne peux pas tester ces requêtes entièrement car j'ai des versions de SQL (MySQL ou SQL Server) qui visiblement ne comprennent pas le WITH.
Je ne vois pas par quoi le remplacer. Peut-être par un create view ?

Pareil pour Coalesce, je ne connaissais pas. Mais apparemment c'est à partir de SQL Server 2008.
Il faut que je voie car sur le serveur original je suis bien en SQL Server 2012.

Sur 2012, je n'ai pas d'erreur avec le With. Il me reste une erreur de conversion de type varchar en numeric. Je pense que je ne suis pas loin. Je fais le tour des types de données pour voir.

Merci

Le with est arrivé sur SQL-Server 2005 donc il ne devrait pas y avoir de problème.
Par contre le parseur de SSMS peut être tatillon, écrivez votre with en commençant par un point virgule :

Code:

---

;WITH Tmp AS (

---

Bon, j'obtiens un résultat. Il n'est pas encore correct (j'ai des 0 partout)
Le ; au début ne gène pas.
Le With je ne peux effectivement pas le tester car mon serveur de test est un sql server 2000. Du coup, je travaille directement sur le serveur de prod.

Apparemment j'ai des convert qui ne semblent pas faire ce que je veux.
En enlevant les convert, faux espoir, on s'approche mais c'est pas ça.
En résultat j'ai :
0.2 0.3
0.1 0.3
0.2 0.2
Il me manquerait juste la somme des lignes ayant un critère null (ou vide). La ligne référente en fait.

Tiens ça serait peut-être le problème : il veut un convert sur le critère, mais pour avoir des valeurs je les ai enlevés des autres champs. Mais laissé sur le critère sinon j'ai :
Msg*8114, Niveau*16, État*5, Ligne*1
Erreur de conversion du type de données varchar en numeric.

Du coup, il ne prendrait pas en compte les lignes ayant un critère vide ? (à cause du convert)

Je n'arrive pas à l'adapter car en fait le critère vient d'une autre table. (dans mes données réelles).
Du coup si je l'enlève j'obtiens :

Code:

---

1 2 3 4 5 6 7

WITH Tmp AS ( SELECT id, ligne_ref, val, SUM(val) OVER(PARTITION BY COALESCE(ligne_ref, id)) AS somme FROM MaTable ) SELECT val, somme FROM Tmp WHERE ligne_ref <> ''

---

donc avec l'erreur
Msg*8114, Niveau*16, État*5, Ligne*1
Erreur de conversion du type de données varchar en numeric.

a priori, la colonne critere est de type numeric.

Qu'avez vous dans cette colonne quand il n'y a pas de critère ?

Si comme je le pense vous avez NULL, alors il suffit de remplacer le filtre par :

Code:

---

WHERE critere IS NOT NULL

---

Merci
En mettant IS NOT NULL je n'ai plus l'erreur.[EDIT] Et je peux enlever le convert[/EDIT]
Mais ça ne me fait pas la somme des 3 lignes, uniquement les lignes qui ont un critère non vide. Les lignes référentes ne sont pas prises en compte.
Merci

avez vous bien mis le filtre dans la requête principale ?

Postez votre requête finale, car visiblement elle est un peu plus complexe que celles données en exemple.

Elle est effectivement plus complexe.
Je vais voir pour l'élaguer pour n'avoir que les infos concernant mon problème.