Discussion :

[tantemay]

Bonjour,

Pour un site de vente, j'ai besoin de récupérer la quantité des articles en stock mais en déduisant les quantités déjà réservées pour les commandes en cours.

Exemple :
Je vends un article dont le stock est de 10 unités. Deux clients passent commandes. L'un de 1 unité, l'autre de 2 unités. La quantité disponible pour d'autres commandes est donc de 10-1-2 = 7 unités. C'est cette valeur que je veux récupérer.

J'ai une première requête qui récupère le stock de l'article. Voici la requête (simplifiée). Notez que les stocks sont à plusieurs endroits, d'où la clause GROUP BY :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT SUM(Quantite) AS 'TotalStock'
FROM Stock
GROUP BY IdArticle
HAVING IdArticle = 123

Pour reprendre l'exemple, cette requête renvoie 10.


Et la deuxième requête qui récupère la somme des quantités commandées, toutes commandes confondues :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT SUM(Quantite) AS 'TotalCommandes'
FROM LigneCommande
GROUP BY IdArticle
HAVING IdArticle = 123

Toujours pour l'exemple, cette requête renvoie 3.

Comment combiner ces deux requêtes pour avoir le résultat de la première moins la seconde ?

Merci.

[Olivier Dufour]

Bonsoir,

Je vous propose d'utiliser un UNION et de faire la somme dessus :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
 
Select SUM(Qte) AS 'Qte Disponible'
FROM (
    SELECT Quantite AS 'Qte'
       FROM Stock
    WHERE IdArticle = 123
    UNION ALL
    SELECT -Quantite AS 'Qte'
      FROM LigneCommande
    WHERE IdArticle = 123
)

Veuillez noter :
L'utilisation d'un WHERE et non d'un HAVING
Le moins ( - ) sur les quantités commandées, qu'il faut soustraire

Cdlt,
OD

[SQLpro]

Beaucoup d'horreurs dans cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT SUM(Quantite) AS 'TotalStock'
FROM Stock
GROUP BY IdArticle
HAVING IdArticle = 123

En effet un GROUP BY sans reprise de IdArticle dans le SELECT ne veut pas dire grand chose généralement... Mais passons, du fait de votre remarque.
En revanche l'utilisation du HAVING est particulièrement stupide. En effet un HAVING filtre les résultats d'une requête tandis que le WHERE filtre les données des tables. En utilisant le HAVING à la place du WHERE vous forcer le moteur à traiter toutes les lignes pour n'en retenir après exécution de la requête qu'une seule.
Commencez déjà par récrire votre requête avec un WHERE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT SUM(Quantite) AS 'TotalStock'
FROM   Stock
WHERE  IdArticle = 123
GROUP BY IdArticle

Ensuite lorsque vous voulez faire des calculs sur plusieurs résultats distinc, il suffit d'utiliser des sous requêtes, comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT SUM(Quantite) - (SELECT SUM(Quantite)
                        FROM   LigneCommande
                        WHERE  IdArticle = 123
                        GROUP  BY IdArticle) AS Ecart
FROM   Stock
WHERE  IdArticle = 123
GROUP  BY IdArticle

Et c'est encore mieux si vous corrélez les deux requêtes pour éviter d'avoir deux fois le critère de filtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT SUM(Quantite) - (SELECT SUM(Quantite)
                        FROM   LigneCommande AS Tin
                        WHERE  Tin.IdArticle = Tout.IdArticle
                        GROUP  BY IdArticle) AS Ecart
FROM   Stock AS Tout
WHERE  IdArticle = 123
GROUP  BY IdArticle

Et pour apprendre le SQL, mon site web, comme mon ouvrage peuvent vous y aider !

A +

[tantemay]

Merci pour vos réponses.

En fait, j'ai utilisé un article dans la requête juste pour l'exemple. En réalité, j'aimerais avoir le stock de tout mes articles en une seule requête.

Je n'arrive pas à adapter vos requêtes pour avoir tous les articles en même temps.

[SQLpro]

1) supprimez le WHERE avec référence d'article
2) ajouter la référence d'article dans la clause SELECT du SELECT principal

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT SUM(Quantite) - (SELECT SUM(Quantite)
                        FROM   LigneCommande AS Tin
                        WHERE  Tin.IdArticle = Tout.IdArticle
                        GROUP  BY IdArticle) AS Ecart,
       IdArticle
FROM   Stock AS Tout
GROUP  BY IdArticle

A +

[tantemay]

Cette solution ne fonctionne que partiellement.

En effet, dans le cas où un article n'est présent dans aucune commande, la requête renvoie une quantité NULL.

Pour pallier à ça, j'ai rajouté un SUM(Quantite) dans le SELECT principal. Ma requête renvoie ainsi le stock actuel et le stock avec les quantités commandées déduites.

Peut-être y a t-il une solution plus propre ?

[SQLpro]

La solution propre consiste à utiliser une fonction de denullification comme COALESCE....
Lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/null/

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT SUM(Quantite) - 
       COALESCE((SELECT SUM(Quantite)
                 FROM   LigneCommande AS Tin
                 WHERE  Tin.IdArticle = Tout.IdArticle
                 GROUP  BY IdArticle), 0) AS Ecart,
       IdArticle
FROM   Stock AS Tout
GROUP  BY IdArticle

Bref, apprenez SQL, c'est un VRAI langage ! Mon site web comme mon bouquin peuvent vous y aider....

A +

[tantemay]

Ca fonctionne merci !