2
votes

JOINT GAUCHE de deux tables, SUM, GROUP

J'ai deux tables, INPUT et OUTPUT.

Table INPUT avec les colonnes ID, productName, QTY, buyPrice, sellPrice. Table OUTPUT avec les colonnes ID, productName, QTY. Les deux peuvent avoir plusieurs entrées avec le même nom de produit et une quantité différente (ou identique). OUTPUT ne peut pas avoir productName qui n'apparaît pas dans la table INPUT en premier.

J'essaye d'interroger les deux tables et d'obtenir le résultat final - quelque chose comme ceci: productName, SUM (QTY) de la table INPUT (GROUP BY productName), productName, SUM (QTY) de OUTPUT (GROUP BY productName)

Exemple:

INPUT

XXX

SORTIE

SELECT a.productName , SUM(a.QTY), b.productName , SUM(b.QTY)
FROM input a LEFT JOIN output b
ON a.productName = b.productName 
GROUP BY a.productName

LE RESULTAT DEVRAIT ETRE

SELECT a.productName , SUM(a.QTY), a.buyPrice , a.sellPrice
FROM input a
GROUP BY a.productName

Il est facile de faire la SOMME et GROUP quand ce n'est qu'une table, mais quand j'essaye de JOINDRE GAUCHE les deux tables et GROUP by productName, j'obtiens des valeurs erronées pour SUM. Où me suis-je trompé?

Partie facile:

productName | SUM(QTY)INPUT | productName | SUM(QTY)OUTPUT
 dress 007  |      18       |  dress 007  |        6
 shirt 001  |       6       |  shirt 001  |        5
   hat 008  |       2       |     null    |       null

Erreur:

ID | productName | QTY
1  |  dress 007  |  4
2  |  shirt 001  |  2
3  |  dress 007  |  1
4  |  dress 007  |  1
5  |  shirt 001  |  3

sql mysql sum join

1 commentaires

@ Fraise, je ne vois pas comment la référence en double s'applique ici? Ici, nous avons deux tableaux qui doivent être combinés. L'autre question est de savoir comment obtenir d'autres colonnes après le regroupement. Est-ce que je manque quelque chose?

4 Réponses :

0
votes

Je ferais l'agrégation dans les sous-requêtes et je les rejoindrais.

SELECT input.productname,
       input.quantity input_quantity,
       output.quantity output_quantity
       FROM (SELECT productname,
                    sum(qty) quantity
                    FROM input
                    GROUP BY productname) input
            LEFT JOIN (SELECT productname,
                              sum(qty) quantity
                              FROM output
                              GROUP BY productname) output
                      ON output.productname = input.productname;

1 commentaires

Vous ne devez effectuer qu'une seule agrégation dans une sous-requête. Cela suffit pour résoudre le problème de duplication des lignes.

2
votes

Vous pouvez utiliser un union all :

SELECT  IF(sum(input) , productName, null) productName, sum(input) input,
        IF(sum(output), productName, null) productName, sum(output) output
FROM    (
            SELECT productName, QTY input, null output
            FROM input
            UNION ALL
            SELECT productName, null, QTY
            FROM output
        ) as sub
GROUP BY productName

sqlfiddle

Cela fonctionnera même dans le cas où vous avez une sortie pour un produit, mais aucune entrée. Cela peut se produire lorsque les données concernent des transactions en - disons - un mois, mais où il y a déjà un stock au début de ce mois.

0 commentaires

0
votes

Je pense que union all est la voie à suivre. Je ferais:

select productName, sum(input) as input_qty, sum(output) as output_qty
from ((select productName, qty as input, null as output
       from input
      ) union all
      (select productName, null, qty
       from output
      )
     ) io
group by productName;

Notez que productName n'apparaît qu'une seule fois dans l'ensemble de résultats. Je ne vois aucune raison de l'inclure deux fois. S'il n'y a pas de ligne output (ou pas de input ), alors la qty correspondante sera NULL . p>

0 commentaires

0
votes

Bienvenue dans SO

Il existe un moyen sans sous-requête pour y parvenir. C'est mathématique :)

Puisque vous parcourez 2 tables, vous obtiendrez le résultat multiplié par ID, par Produit. Il suffit donc de diviser la somme par le nombre de noms de produits divisés par les identifiants uniques correspondant à ces produits :)

Donc, quelque chose comme ça fera l'affaire: Schéma (MySQL v5.7)

SELECT I.productName, 
       SUM(I.QTY)/(COUNT(I.productName)/count(distinct I.ID)) 
                                          as "SUM(QTY)INPUT",
       O.productName,                      
       SUM(O.QTY)/(COUNT(O.productName)/count(distinct O.ID))  
                                          as "SUM(QTY)OUTPUT"
FROM INPUT I
LEFT OUTER JOIN OUTPUT O ON I.productName = O.productName
GROUP BY I.productName
ORDER BY 2 DESC;

| productName | SUM(QTY)INPUT | SUM(QTY)OUTPUT | productName |
| ----------- | ------------- | -------------- | ----------- |
| dress 007   | 18            | 6              | dress 007   |
| shirt 001   | 6             | 5              | shirt 001   |
| hat 008     | 2             | null           | null        |

Requête n ° 1

CREATE TABLE INPUT (
  `ID` INTEGER,
  `productName` VARCHAR(9),
  `QTY` INTEGER,
  `buyPrice` INTEGER,
  `sellPrice` INTEGER
);

INSERT INTO INPUT
  (`ID`, `productName`, `QTY`, `buyPrice`, `sellPrice`)
VALUES
  ('1', 'dress 007', '2', '700', '1400'),
  ('2', 'shirt 001', '4', '800', '1900'),
  ('3', 'dress 007', '10', '700', '1500'),
  ('4', 'dress 007', '6', '900', '2900'),
  ('5', 'shirt 001', '2', '750', '1600'),
  ('6', 'hat 008', '2', '300', '600');

CREATE TABLE OUTPUT (
  `ID` INTEGER,
  `productName` VARCHAR(9),
  `QTY` INTEGER
);

INSERT INTO OUTPUT
  (`ID`, `productName`, `QTY`)
VALUES
  ('1', 'dress 007', '4'),
  ('2', 'shirt 001', '2'),
  ('3', 'dress 007', '1'),
  ('4', 'dress 007', '1'),
  ('5', 'shirt 001', '3');

https: //www.db-fiddle. com / f / Dp7yaNkVf3JW2DZrrvL7G / 1

0 commentaires