SQL Abfrage mit 2 JOINS und SUM() zum Bestimmen des Bestandes

[Simuletsplay]

Hallo zusammen,

ich bin gerade dabei eine SQL Abfrage zu machen, die mir den jeweils aktuellen Stand für die Produkte ausgibt.

Folgende Tabellen mit relevanten Parametern:
produkte
- id
- bezeichnung

ankauf
- id
- pid (produkt-id)
- menge
- datum

verkauf
- id
- pid (produkt-id)
- menge
- datum

Ich habe in meiner Datei eine Abfrage gefunden, die leider etwas völlig falsches ausgibt:

SELECT
    produkte.bezeichnung AS produkt,
    SUM(ankauf.menge) - SUM(verkauf.menge) AS inventar
FROM
    ankauf
INNER JOIN produkte ON ankauf.pid = produkte.id
INNER JOIN verkauf ON ankauf.pid = produkte.id
GROUP BY
    produkte.id
ORDER BY
    SUM(ankauf.menge) - SUM(verkauf.menge)
DESC

Daraufhin wollte ich den gerade überarbeiten und habe folgendes versucht: (nur allein Die Ausgabe der An- und Verkäufe - Wenn die Stimmen, dann muss ja auch die Differenz der beiden Zahlen funktionieren [ist quasi nur ein Zwischenschritt])

SELECT
    produkte.bezeichnung,
    SUM(ankauf.menge),
    SUM(verkauf.menge)
FROM
    produkte
INNER JOIN ankauf ON produkte.id = ankauf.pid
INNER JOIN verkauf ON produkte.id = verkauf.pid
GROUP BY
    ankauf.pid,
    verkauf.pid,
    produkte.id

Kann mir jemand sagen, wo der Fehler liegt?

Grüße
Ben

 

[dabadepdu]

Das ist eine von Millionen mySQL Abfragen, die falsche Daten ausgibt, statt einen Fehler.

Wenn Du mit mySQL arbeiten musst und keine Alternative hast, lerne bitte dringend, wie Group By Abfragen richtig gemacht werden! Hier bist Du offenbar in der Lage, noch selbst Du erkennen, dass das Ergebnis falsch ist. Bei mehr Daten fällt das zunehmend schwer.

Die Faustregel lautet:
Gruppiere alle Felder der Ausgabe Spalten, die Du nicht aggregierst. Das gilt immer bei Group By, immer = 98%. Solange Du es nicht verstanden hast bei 100%.

Also:

SELECT
produkte.bezeichnung, > "pure" Ausgabe, gehört ins group by
SUM(ankauf.menge), > aggregiert, kommt nicht ins group by
SUM(verkauf.menge) > aggregiert, kommt nicht ins group by

bleibt das Group By selbst. Laut dem Select oben lautet es lediglich:
GROUP BY produkte.bezeichnung

Du leitest also die Group By Clause aus dem Select Teil ab.

Ein Group By nach Gefühl zu schreiben, bringt Dir idR. einen Fehler bei der Ausführung, außer es ist zufällig richtig. Nur mySQL glaubt zu wissen, was Du fühlst. Es gibt keinen Fehler aus, sondern denkt sich ein beliebiges Ergebnis, wenn die Abfrage falsch formuliert ist.

Also such Dir ein Tutorial, dass Dir Group By erklärt. Am besten, Du übst mit einer anderen Datenbank solange bis 50 Abfragen fehlerfrei laufen. (Kann beliebig mehr sein) Wenn Du weißt, was Du tust, und immer noch keine andere DB nutzen darfst, dann kannst Du danach auch mySQL nehmen.

Ob mit der Abfrage Dein gewolltes Ergebnis erzielt wird oder werden kann, habe ich mir nicht angesehen.

 

[akretschmer]

neben den korrekten Hinweisen von @dabadepdu (in Kurz: verwende statt MySQL lieber PostgreSQL) noch der Hinweis, daß zwei gleich aufgebaute Tabellen (Ankauf und Verkauf) auch Murks ist. Ist Menge negativ, ist es ein Verkauf, sonst Ankauf. So einfach ist das. Auf der Bank werden Konten auch so geführt.

 

[Walter]

zwei gleich aufgebaute Tabellen (Ankauf und Verkauf) auch Murks ist

Ja.

Ist Menge negativ, ist es ein Verkauf, sonst Ankauf

Nein. Wie willst Du dann zB eine Warenrückgabe von einem Ankauf unterscheiden?
Falls Verkauf und Einkauf wirklich gleich sind löst man das mit einem eigenen Feld das die verschiedenen Geschaftsfälle repräsentiert.

 

[akretschmer]

Wie willst Du dann zB eine Warenrückgabe von einem Ankauf unterscheiden?

was für die Aussage über den Lagerbestand egal ist. Aber ja, WENN man das mit betrachten will, dann hast Du natürlich Recht.

 

[ukulele]

Die aktuelle MySQL Version sollte hier aber auch einen Fehler werfen, zumindest glaube ich das (nutze es auch nicht). Daher wäre noch ein Update von MySQL eventuell empfehlenswert.

So aus Neugierde:

Gruppiere alle Felder der Ausgabe Spalten, die Du nicht aggregierst. Das gilt immer bei Group By, immer = 98%. Solange Du es nicht verstanden hast bei 100%.

Ich würde sagen immer = 100%. Oder beziehst du dich auf zusammen gesetzte Zeichenketten, hast du mal ein Beispiel wo es nicht so ist?