Discussion :

[julien7793]

Bonjour,

Je cherche à faire une jointure dans une requête sql sur une même table avec une somme sur un montant.
La table liste l'ensemble des factures et des avoirs d'une entreprise. Un champ "source_avoir" est rempli uniquement s'il s'agit d'un avoir et renvoie l'ID de la facture.
Une facture peut avoir 1 à N Avoirs.
Je chercher à faire une requête qui me liste toutes les factures (même celles qui n'ont pas d'avoir) avec le montant total des avoirs associés à une facture.
Voici les champs utiles de ma table :
- id
- type (1,2) => (1=facture ; 2=avoir)
- montant_facture_ht
- source_avoir

Voici une première requête qui fonctionne mais elles me renvoient QUE les factures avec un ou plusieurs Avoir alors que je souhaite toutes les factures même celles qui n'ont pas d'avoirs :

Code :

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

1
2
3
4
SELECT f1.`id`, f1.`montant_facture_ht`, SUM(f2.`montant_facture_ht`) AS `TOTAL_AVOIR`
FROM `table_facture` f1
LEFT JOIN `llx_facture` f2 ON  f2.`source_avoir` = f1.`id` 
GROUP BY f2.`source_avoir`

J'ai aussi essayé cette requête mais elle ne marche pas :

Code :

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

1
2
3
4
5
6
7
SELECT f1.`id`, f1.`montant_facture_ht`,`TOTAL_AVOIR`
FROM `llx_facture` f1
LEFT OUTER JOIN (
    SELECT f1.`id`, SUM(f2.`montant_facture_ht`) AS `TOTAL_AVOIR`
    FROM `llx_facture` f2
   GROUP BY  f2.`source_avoir`
) f1 ON  f2.`source_avoir` = f1.`id`

[tatayo]

Bonjour,
Je ne comprends pas ta demande, car il y a des incohérences.
Tu dis n'avoir qu'une seule table, alors que la première en utilise 2.
Tu as une colonne (et non un champ) type qui indique s'il s'agit d'un avoir ou d'une facture, et tu ne l'utilises pas dans tes requêtes.

Je pense donc que tu devrais nous donner la description EXACTE des tables (le DDL), un jeux de données des 2 tables et le résultat attendu.

Tatayo.

[Michel.Priori]

Bonsoir,

Comme vous le dites pour une ligne de facture il y a 0 ou + lignes d'avoirs.

Pour produire le CA_operationnel (différent du CA_comptable) il faut agréger les avoirs.
A partir de là c'est une simple jointure externe

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
with total_avoir as
    (select source_avoir 
            , sum (montant_facture_ht) as tot_avoir
     from table_facture
     where type =2
     group by source_avoir
    )
Select fact.*
    , total_avoir.tot_avoir
from table_facture fact 
        left outer join total_avoir
            on total_avoir.source_avoir=fact.id
;

A noter : cette syntaxe est ISO 2005. MySQL n'y est compatible que depuis très peu ...
Le contournement est de faire un sous select dans la clause from.

[julien7793]

Bonjour et merci de vos réponses.

Voici un exemple de la table facture :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `llx_facture` (
  `id` int(11) NOT NULL,
  `facnumber` varchar(30) NOT NULL,
  `type` smallint(6) NOT NULL DEFAULT '0',
  `montant_facture_ht` double(24,8) DEFAULT '0.00000000',
  `source_avoir` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Déchargement des données de la table `llx_facture`
--
 
INSERT INTO `llx_facture` (`id`, `facnumber`, `type`, `source_avoir`, `montant_facture_ht`) VALUES
(1, 'FA1901-0001', 1, NULL, 2137.00000000),
(2, 'AV1901-0001', 2, 1, 137.00000000),
(3, 'FA1901-0002', 1, NULL, 1.00000000),
(4, 'FA1901-0003', 1, NULL, 1.00000000),
(5, 'FA1901-0004', 1, NULL, 400.00000000),
(6, 'FA1901-0005', 1, NULL, 700.00000000),
(7, 'FA1901-0006', 1, NULL, 900.00000000),
(8, 'AV1901-0002', 2, 1, -50.00000000),
(9, 'AV1901-0003', 2, 7, -200.00000000);

@Michel : la fonction WITH ne semble pas fonctionner dans PHPMyAdmin ... J'ai une croix rouge sur la première ligne.

[Michel.Priori]

Bonjour

la fonction WITH ne semble pas fonctionner dans PHPMyAdmin

Non, le problème ne vient pas de PHPMyAdmin; Le problème vient de la version de votre Mysql ; PHPMyAdmin n'est qu'un utilitaire, c'est certainement celui dont vous avez l'habitude mais ce n'est qu'un programme (=IHM) ; MySQL est un demon/service ; C'est Mysql qui traite et, donc accepte, ou non, la syntaxe.

Pour mémoire :
MySQL est passé en licence GPL à partir de la version 3.23.19 (juin 2000)
Version 4.0 : première version en octobre 2001, stable depuis mars 2003
Version 4.1 : première version en avril 2003, stable depuis octobre 2004
Version 5.0 : première version en décembre 2003, stable depuis octobre 2005
Version 5.1 : première version en novembre 2005, Release Candidate distribuée depuis septembre 2007
Version 5.2 : distribuée en avant-première (ajout du nouveau moteur de stockage Falcon) en février 2007, cette ligne a ensuite été renommée 6.0
Version 5.5 : Version stable depuis octobre 2010
Version 5.6 : Version stable depuis février 2013
Version 5.7 : Version stable depuis octobre 2015
Version 6.0 : première version alpha en avril 2007, abandonnée depuis le rachat de MySQL par oracle en décembre 2010
Version 8.0 : Version stable depuis avril 2018

La syntaxe With (ce n'est pas une fonction) est acceptée à partir de la version de 2018.
Au passage, on trouve dans cette version, des fonction de fenêtrage qui sont tout autant utiles que la syntaxe with (qui permet la récursivité en plus qu'une certaine clarification du code, truc important quand on relis du code).
"Depuis très peu" ; c'était l'objet de la note.

Comme vous l'avez noté, la version est considérée comme stable depuis avril 2018.
On peut donc la mettre en production maintenant ...

[aieeeuuuuu]

Bonjour,

Non, votre première requête ne fonctionne pas et pourrait vous renvoyer des résultats faux, car le regroupement est incorrect

Elle devrait être comme ceci :

Code :

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

1
2
3
4
5
6
 
 
SELECT f1.id, f1.montant_facture_ht, SUM(f2.montant_facture_ht) AS TOTAL_AVOIR
FROM llx_facture f1
LEFT JOIN llx_facture f2 ON  f2.source_avoir = f1.id 
GROUP BY f1.id, f1.montant_facture_ht

quant à la deuxième, il y a juste quelques petites erreurs, mais elle devrait fonctionner après correction :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT f1.id, f1.montant_facture_ht,TOTAL_AVOIR
FROM llx_facture f1
LEFT OUTER JOIN (
    SELECT f2.source_avoir, SUM(f2.montant_facture_ht) AS TOTAL_AVOIR
    FROM llx_facture f2
   GROUP BY  f2.source_avoir
) f2 ON  f2.source_avoir = f1.id