Korrelierte Teilanfrage mysql

Question

Also habe ich eine Tabelle mit Produkten

Produkt-ID | Produktname
===========+===============
1          | Taschentücher
2          | Glas

Ich habe eine Verkaufstabelle

Verkaufs-ID | Produkt-ID | Menge | Preis
===========+============+==========+=============
1          | 1          | 1        | 55
2          | 2          | 1        | 60

und ich habe eine Einkaufstabelle

Batch-ID | Gesamtwert | Menge | Produkt-ID
=========+=============+==========+==================
1        | 100         | 100      | 1
2        | 10          | 50       | 2
3        | 1           | 1        | 2

Also versuche ich, den Gewinn auf der Grundlage des durchschnittlichen Preises unter Verwendung der Abfrage zu berechnen

SELECT tblsale.product_id, 
       tblproduct.product_name, 
       SUM(tblsale.`quantity`) qty,
       SUM(tblsale.`Price`*tblsale.`quantity`) sales, 
       (SELECT sum(total_value) / sum(quantity) VWAP 
        FROM tblpurchases 
        WHERE product_id = tblsale.product_id) average_price, 
       (average_price * qty) cost, 
       (sales-cost) profit 
FROM   tblsale, tblproduct 
WHERE tblproduct.product_id = tblsale.`product_id` 
GROUP by tblsale.`product_id`

Aber ich kann es irgendwie nicht zum Laufen bringen, ich bekomme eine Meldung 'Durchschnittspreis' ist eine unbekannte Spalte, wie sollte ich die Abfrage korrekt strukturieren

Answer 1

SQL unterstützt nicht das Referenzieren eines Spaltenalias im selben SELECT-Block - deshalb gibt Ihre average_price-Spalte den Fehler 1054 zurück. Sie müssen entweder die erforderliche Operation in einem Unterselect, abgeleiteten Tabelle/Inline-View durchführen oder die zugrunde liegende Logik bei Bedarf erneut verwenden. Hier ist ein Beispiel für die Wiederverwendung von Logik:

   SELECT prod.product_id, 
          prod.product_name, 
          SUM(s.quantity) qty,
          SUM(s.Price * s.quantity) sales, 
          SUM(pur.total_value) / SUM(pur.quantity) average_price, 
          SUM(pur.total_value) / SUM(pur.quantity) * SUM(s.quantity) cost, 
          SUM(s.Price * s.quantity) - (SUM(pur.total_value) / SUM(pur.quantity) * SUM(s.quantity)) profit 
     FROM tblproduct prod 
LEFT JOIN tblsale s ON prod.product_id = s.product_id
LEFT JOIN tblpurchases pur ON pur.product_id = prod.product_id
 GROUP BY s.product_id

Meine Abfrage verwendet die ANSI-92 JOIN-Syntax, die ich gegenüber der von Ihrer Abfrage verwendeten ANSI-89-Syntax empfehle. Sehen Sie diese Frage für weitere Details.

Answer 2

Wie sind Sie zu dieser Abfrage gekommen? Es ist völlig daneben.. Wenn Sie eine Abfrage schreiben, fangen Sie klein an und bauen dann darauf auf. Die Abfrage, die Sie jetzt haben, ist ein komplettes Durcheinander und weit davon entfernt, gültig zu sein, es gibt überall zufällige Klammern darin.

Um anzufangen, verwenden Sie Einrückung, um Ihre Abfrage lesbar zu machen

    SELECT p.product_id, p.product_name
         , SUM(s.quantity) number_of_sales
         , SUM(s.price) total_profit
         , SUM(pu.quantity) purchase_quantity
         , SUM(pu.value) purchase_value
         , (SUM(pu.quantity) - SUM(s.quantity)) number_in_stock
         , (SUM(s.price) - SUM(pu.value)) profit
         , (SUM(pu.value) / SUM(pu.quantity)) avarage_purchase_price
      FROM product p
 LEFT JOIN sales s ON s.product_id = p.product_id
 LEFT JOIN purchase pu ON pu.product_id = p.product_id         
  GROUP BY s.product_id, pu.product_id

"Aber ich kann es einfach nicht zum Laufen bringen, ich bekomme einen 'Durchschnittspreis' ist eine unbekannte Spalte, wie sollte ich die Abfrage korrekt strukturieren"

Was ist 'Durchschnittspreis'? Wie möchten Sie, dass der Durchschnittspreis berechnet wird? Und dasselbe für 'durchschnittliche Kosten'