SQL Eintrag durch Grundmenge dividieren und dann aufaddieren

[sql-schüler]

Hallo zusammen,

ich habe eine Tabelle, in der verkaufte Artikel gelistet sind. Diese besteht mitunter aus folgenden Attributen:

Artikelname

Menge

Artikelbestandteile

EreignisID

Artikelvariante

Die Tabelle ist listet die Anzahl der verkauften Artikel, indem die Menge der Artikelbestandteile erhöht wird und sieht dann beispielsweise wie folgt aus:

Artikelname	Menge	Artikelbestandteile	EreignisID	Artikelvariante
X	7	X1	1	a
X	14	X2	1	a
X	35	X3	1	a
X	7	X4	1	a
Y	6	Y1	2	a
Y	3	Y2	2	a
Y	9	Y3	2	a
X	1	X1	3	b
X	2	X2	3	b
X	5	X3	3	b
X	1	X4	3	b
X	1	X5	3	b
X	1	X6	3	b
Y	8	Y1	4	a
Y	4	Y2	4	a
Y	12	Y3	4	a

Die Tabelle besteht natürlich aus vielen weiteren Einträgen, doch die aufgeführten Einträge dürften mein Problem ausreichend veranschaulichen.

Es existiert noch eine weitere Tabelle, in der die Bestandteile mit ihrer Menge aufgelistet sind.

Beispielsweise sieht man an Artikel "X" das er in den ersten Zeilen 7x verkauft wurde, da man weiter unten an dem anderen Artikel "X" sieht das Bestandteil X1 aus einem Teil besteht oder X2 aus 2 Teilen.

Nun hätte ich gerne eine Abfrage oder ähnliches erstellt, in der ich den Artikelnamen erhalte, wie oft dieser verkauft wurde und um welche Artikelvariante es sich handelt.

Weiter unten erscheint der Artikel "X" ein weiteres Mal. Hier unterscheidet sich aber die Artikelvariante, wodurch zwei weitere Bestandteile dazu kommen, nämlich X5 und X6.

Ich wäre sehr dankbar, wenn ihr hilfreiche Ansätze zu meinem Problem habt

 

[DonnyDepp]

"Nun hätte ich gerne eine Abfrage oder ähnliches erstellt, in der ich den Artikelnamen erhalte, wie oft dieser verkauft wurde und um welche Artikelvariante es sich handelt."

wozu braucht man für diese anforderung die "artikelbestandteile"?
denn sonst ist das ziemlich easy. musst nur ne gruppierung auf (artikelname, artikelvariante) machen mit summierung über die menge.

 

[sql-schüler]

Ich würde nach der Abfrage diese Tabelle wollen:

Artikelname	verkaufte Menge	Artikelvariante
X	7	a
X	1	b
Y	7	a

Ich weiß nur nicht wie ich aus den Einträgen an die verkaufte Menge komme. Irgendwie muss die Ereignisse durch die Grundmenge, die Artikel X enthält, dividieren.

 

[Alchemist]

Kannst du eventuell das gewünschte Ergebnis für die Tabelle im 1. Post mal genau angeben? Ich verstehe leider nicht was du willst.

 

[Madman1209]

Hi,

entweder mit GROUP BY / COUNT oder mit einer WINDOW FUNCTION.

SELECT Artikelname, COUNT(Artikelname) AS verkaufte Menge, Artikelvariante FROM table GROUP BY Artikelname, Artikelvariante

Müsste eigentlich ein brauchbares Ergebnis liefern, jedenfalls war's bei mir im Test gerade in Ordnung

VG,
Mad

 

[sql-schüler]

Müsste eigentlich ein brauchbares Ergebnis liefern, jedenfalls war's bei mir im Test gerade in Ordnung

Kannst du dein Ergebnis Set hier bitte reinstellen?

 

[Madman1209]

Hi,

nachdem ich nicht mit identischen Werten sondern Beispielwerten gearbeitet habe bringt dir das Set vermutlich wenig. Aber die Ergebnismenge war korrekt (zweimal "X" mit "a", einmal "X" mit "b", dreimal "Y" mit "a")

Was genau klappt denn nicht bzw. was erhältst du denn für ein Ergebnis?

Nur noch zur Sicherheit: MySQL oder MS-SQL?

VG,
Mad

 

[sql-schüler]

Kannst du eventuell das gewünschte Ergebnis für die Tabelle im 1. Post mal genau angeben? Ich verstehe leider nicht was du willst.

Ich möchte beispielsweise eine Tabelle wie in meinem 2. Post. Durch die Menge der Bestandteile (die ja erhöht wird wenn mehr als ein Artikel verkauft wurde, was man an X1 gut sieht weil der Artikel X eigentlich nur aus einem X1 besteht) möchte ich auf die verkaufte Menge schließen.

Ergänzung (16. August 2021)

nachdem ich nicht mit identischen Werten sondern Beispielwerten gearbeitet habe bringt dir das Set vermutlich wenig. Aber die Ergebnismenge war korrekt (zweimal "X" mit "a", einmal "X" mit "b", dreimal "Y" mit "a")

Aber X in der Variante a besteht doch schon mindestens aus einem Artikelbestandteil X1, also sehe ich ja an der Eintragstabelle, dass dieser 7 mal verkauft wurde.

Ich habe die Mengen der Artikelbestandteile extra so gewählt, damit man erkennt, dass die Menge der verkauften Artikel über die Mengen der einzelnen Bestandteile gezählt wird.

An Artikel Y in der Variante a sieht man das man auf den kleinsten Nenner kommen muss, was dort für EreignisID_2 dann 3 Stück wären und bei der EreignisID_4 dann 4 Stück weswegen ich bei meinem 2. Post auf insgesamt 7 komme. Und diese Logik dahinter hätte ich gerne in einer Abfrage weil ich sonst nicht auf meine verkaufte Mengen komme.

 

[marcOcram]

Wenn ich es richtig verstanden habe und ich noch nicht im Schlaf angekommen bin, möchtest du folgendes haben:

SELECT
  Artikelname,
  Artikelvariante,
  SUM(MinimaleMenge) AS VerkaufteMenge
FROM (
  SELECT
    Artikelname,
    Artikelvariante,
    MIN(Menge) AS MinimaleMenge
  FROM
    <tableName>
  GROUP BY
    Artikelname,
    Artikelvariante,
    EreignisID
) AS MinimalErgebnis
GROUP BY
  Artikelname,
  Artikelvariante

Als erstes wird pro Artikelname, Artikelvariante und EreignisID der kleinste Nenner bestimmt. Danach wird dieser kleinste Nenner pro Artikelname und Artikelvariante aufsummiert.

 

[AW4]

select Artikelname, Artikelvariante, sum(Menge) verkaufteMenge
from (select Artikelname, Artikelvariante, min(Menge) Menge
      from Tabelle
      group by Artikelname, Artikelvariante, EreignisID)
group by Artikelname, Artikelvariante

Das liefert nur für die Artikel akkurate Ergebnisse, die garantiert mindestens einen Artikelbestandteil besitzen, der nur genau einmal in der Stückliste/Materialliste/Bill Of Materials(BOM)/o.ä. vorkommt.
Für Listen in denen alle Artikelbestandteile immer mehrfach oder/und anteilig vorkommen, reichen die Daten in dieser Tabelle nicht aus, um ein jeder Zeit korrektes Ergebnis berechnen zu können.
Es könnten Heuristiken verwendet werden, die auf der Datenbasis dieser Tabelle versuchen, "akkuratere" Ergebnisse zu liefern, aber da rate ich dann doch eher dazu, herauszufinden wo die entsprechenden Stücklisten/Materialliste/Bill Of Materials(BOM)/o.ä. definiert sind, um mit diesen Zusatzinformationen dann immer valide Ergebnisse ermitteln zu können.

Um das etwas besser zu veranschaulichen:
Es ist aus deinen Angaben nicht ersichtlich, ob es Artikel Z geben könnte, der aus Artikelbestandteil Z1 und einem weiteren Artikelbestandteil Z1 besteht.
Je nachdem wie dann das Ereignis in dieser Tabelle geführt wird, kann das zu fehlerhaften Ergebnissen in diesen Fällen führen:

• Würden in dem Fall jeweils ein Datensatz für Z1 und ein weiterer für den anderen Z1 erstellt werden, in dem jeder Bestandteil einzeln aufgeschlüsselt wird, stimmt das Ergebnis.
• Würde in dem Fall das Ereignis zu einem Datensatz zusammengefasst werden, würde Artikel Z immer doppelt gezählt und das Ergebnis wäre für diese Fälle falsch.

Im anderen Fall, in dem die Listen geteilte Artikelbestandteile besitzen können, ist das Ergebnis immer falsch.
Gibt es z.B. Artikel V, der aus 1x V1, 7x V2 und 0,5xV3 besteht, sorgt das Min() im Derived Table dafür, dass das Ergebnis für V immer um die Hälfte falsch ist.

Edit:
@marcOcram war da mit derselben Idee etwas schneller

 

[sql-schüler]

vielen Dank erstmal für eure beiden Vorschläge

aber da rate ich dann doch eher dazu, herauszufinden wo die entsprechenden Stücklisten/Materialliste/Bill Of Materials(BOM)/o.ä. definiert sind, um mit diesen Zusatzinformationen dann immer valide Ergebnisse ermitteln zu können.

Es gibt noch eine zweite Tabelle, in der die Komponenten oder eben Artikelbestandteile mit ihren jeweiligen Mengen gelistet sind.

Das liefert nur für die Artikel akkurate Ergebnisse, die garantiert mindestens einen Artikelbestandteil besitzen, der nur genau einmal in der Stückliste/Materialliste/Bill Of Materials(BOM)/o.ä. vorkommt.

Sobald ich also einen Artikel habe, bei dem die kleinste Menge eines Artikelbestandteils 2 ist, würde dieser Ansatz nicht mehr funktionieren?

Ich werde das schnellstmöglich mit meiner eigentlichen Datenbank testen und dann hier berichten

 

[sql-schüler]

Hallo zusammen, leider habe ich keine vernünftigen Ergebnisse mit der Abfrage erzielen können. Ich denke das liegt an den von @AW4 aufgeführten Problemen.

Es ist aus deinen Angaben nicht ersichtlich, ob es Artikel Z geben könnte, der aus Artikelbestandteil Z1 und einem weiteren Artikelbestandteil Z1 besteht.

Solche Artikel gibt es und diese werden beispielsweise in der Stückliste unter dem Artikelbestandteil Z1 mit der Menge 2 aufgeführt.

• Würden in dem Fall jeweils ein Datensatz für Z1 und ein weiterer für den anderen Z1 erstellt werden, in dem jeder Bestandteil einzeln aufgeschlüsselt wird, stimmt das Ergebnis.
• Würde in dem Fall das Ereignis zu einem Datensatz zusammengefasst werden, würde Artikel Z immer doppelt gezählt und das Ergebnis wäre für diese Fälle falsch

Leider ist der zweite Punkt bei mir der Fall und so würde Z1 mit all seinen anderen Bestandteilen einen Datensatz haben und je nach verkaufter Menge erhöhen, beispielsweise Artikel Z würde 4 mal verkauft werden, dann würde sich der Datensatz mit Z1 auf 8 erhöhen und alle weiter Bestandteile auch um den Faktor 4.

Im anderen Fall, in dem die Listen geteilte Artikelbestandteile besitzen können, ist das Ergebnis immer falsch.
Gibt es z.B. Artikel V, der aus 1x V1, 7x V2 und 0,5xV3 besteht, sorgt das Min() im Derived Table dafür, dass das Ergebnis für V immer um die Hälfte falsch ist.

Auch das ist leider Fall, da es viele Artikel gibt, deren Bestandteile in Gramm angegeben werden und somit Kommazahlen sind.

aber da rate ich dann doch eher dazu, herauszufinden wo die entsprechenden Stücklisten/Materialliste/Bill Of Materials(BOM)/o.ä. definiert sind, um mit diesen Zusatzinformationen dann immer valide Ergebnisse ermitteln zu können.

Welchen Weg müsste ich da verfolgen? Ich bin am Verzweifeln mit dieser Tabelle und freue mich über jeden möglichen Vorschlag eurerseits!

 

[AW4]

Du musst die Tabelle mit den Informationen über die Stücklisten/Materiallisten/Bill Of Materials(BOM)/o.ä. für die einzelnen Bestandteile mit der Ereignistabelle joinen.
Der Rest bleibt ähnlich.

In der Art z.B.:

SELECT Artikelname, Artikelvariante, SUM(Menge) verkaufteMenge
FROM (SELECT bom.Artikelname, bom.Artikelvariante, AVG(IFNULL(e.Menge, 0) / bom.Menge) Menge
      FROM BOMTabelle bom
      LEFT JOIN EreignisTabelle e ON e.Artikelname = bom.Artikelname
                                 AND e.Artikelvariante = bom.Artikelvariante
                                 AND e.Artikelbestandteile = bom.Artikelbestandteile
      GROUP BY bom.Artikelname, bom.Artikelvariante, bom.EreignisID)
GROUP BY Artikelname, Artikelvariante

Je nach DBMS wirst du IFNULL() mit ISNULL(), NVL(), o.ä. ersetzen müssen.
Kann aber auch sein, dass diese Funktion gar nicht notwendig ist, das habe ich nicht ausprobiert.

Dieses SQL wiederum liefert jetzt nur dann auf jeden Fall akkurate Ergebnisse, wenn in der Ereignistabelle die Mengen immer im entsprechenden Verhältnis zu den Mengen in den Stücklisten/Materiallisten/Bill Of Materials(BOM)/o.ä. hinterlegt sind.
Ist das der Fall, liefert IFNULL(e.Menge, 0) / bom.Menge für jeden einzelnen Datensatz desselben Ereignisses dieselbe Menge und die darauffolgende Summierung ergibt die Gesamtsumme der Verkäufe.

Zwei potentielle Problem gibt es jetzt aber auch hier:

• Wird/wurde ein Artikel nicht vollständig (z.B. nur Z1 und Z2 statt alle Bestandteile Z1, Z2, Z3 und Z4) oder mit abweichenden Mengenverhältnissen (2xZ1 und 5xZ2 statt 2x(1xZ1 und 2xZ2)) verkauft, könnte das Ergebnis nicht 100%ig "richtig" sein.
  Wobei hier "100%ig richtig" erst einmal definiert werden müsste.
  Das AVG() löst dieses Problem indem die pro Ergebnis verkaufte Durchschnittsmenge der Artikelbestandteile ermittelt wird.
  Sind die einzelnen Artikelbestandteile aber sehr unterschiedlich oder es soll eine andere Gewichtung einfließen, so müsste man das in die Rechnung IFNULL(e.Menge, 0) / bom.Menge noch einbringen, um ein in allen Lebenslagen "richtiges" Ergebnis zu bekommen.
  Gewichtung könnte hier z.B. über das Gewicht, die Größe oder die Kosten eingebracht werden.
  Macht alles aber natürlich noch etwas komplizierter und wahrscheinlich langsamer.
• Werden oder wurden die Informationen der einzelnen Stücklisten/Materiallisten/Bill Of Materials(BOM)/o.ä. geändert, "verfälscht" das u.U. das Ergebnis.
  Wobei das Ansichtssache ist.
  Wenn man sagt, dass das Ergebnis nach Änderung eben dem Ergebnis des aktuellen Standes der Stammdaten entspricht, ist das kein Problem.
  Wenn man sagt, dass das Ergebnisse vergangener Tage verfälscht oder eben falsch wiedergibt, hat man ein Problem und muss für eine Historie der einzelnen Stücklisten/Materiallisten/Bill Of Materials(BOM)/o.ä. sorgen und dann darüber die Ergebnisse berechnen.

Edit:
Kleiner Tippfehler...
Es muss natürlich GROUP BY bom.Artikelname, bom.Artikelvariante, bom.EreignisID statt GROUP BY e.Artikelname, e.Artikelvariante, e.EreignisID heißen.

 

[sql-schüler]

Vielen Dank erstmal für deine Antwort. Leider kann ich diesen Ansatz nicht umsetzen, da die BOM nicht alles beinhaltet. @AW4

Die Artikelbestandteile sind aufgeteilt in zwei Tabellen. Zu erst gibt es eine Tabelle, in der jedes Teil gelistet ist mit seiner Teilenummer. Darin ist keine Menge enthalten, weil hier jedes Teil genau 1x aufgeführt ist.

Danach gibt es eine Tabelle in der die jeweiligen einzelnen Teile als Komponenten zusammengeführt werden mit der entsprechenden Artikelnummer, Teilenummer und der gebrauchten Menge.

Zudem habe ich eine weitere Tabelle, in der die Artikelnummer und den jeweiligen Nummern der Artikelvariante gespeichert sind(Artikel_Variante). Demzufolge gibt es auch die Tabelle zu den Artikelvarianten mit der Nummer und der Beschreibung(Tabelle_Varianten).

Jetzt habe ich mit mehreren JOIN-Verknüpfungen versucht, die Artikelnummer, (Bestand)-Teilenummer und die Menge abzufragen. Das wollte ich dann auf den obigen Ansatz übertragen und die Abfrage entsprechend abändern, leider ohne Erfolg.

SELECT artikel.artikelnummer, teile.teilenummer, komponenten.menge
FROM (artikel JOIN komponenten ON artikel.idArtikel = komponenten.idArtikel)
              JOIN teile ON komponenten.idTeile = teile.idTeile

Ich wollte irgendwie meine eigene BOM mittels Abfragen erstellen, da ich die benötigten Spalten nicht in einer Tabelle habe und dann irgendwie mit deinem Ansatz verbinden. Was fehlt mir denn noch? Oder kommt es mit meiner Idee überhaupt nicht zu einer Lösung?

Vielen Dank schon mal!