Select mit SubSelect summieren

[uwewo]

Gutem Morgen,

mit folgender Abfrage, erhalte ich die Menge aller Stücklisten-Artikel die im Jahr 2007 für den Artikel 4030.012 verwendet wurden.

SQL-Code:

SELECT a.ArtNr,a.ArtBez, a.Menge * 
(
SELECT SUM(b.GutStueck) 
FROM OEE_Produktion AS b
WHERE a.ArtNr = b.ArtNr
AND Year(b.Datum) = 2007
) AS Menge, a.MengenEinheit

FROM OEE_StckList AS a 
WHERE a.ArtNr = '4030.012'

ORDER BY a.ArtNr

Nun meine Frage, sieht hier jemand eine Möglichkeit das ganze so zu erweitern, dass ich für alle Artikel aus der Produktionstabelle die Mengen erhalte, und diese auch summiert werden. Oder komme ich um eine zusätzliche Abfrage nicht herum?
Jeder Stücklistenartikel, kann natürlich in jedem Artikel aus der Produktionstabelle vorkommen.

[NormanNG]

Hi,

ich kenn MySQL selbst nicht, aber evtl. geht es so:

SQL-Code:

SELECT a.ArtNr,a.ArtBez, a.Menge, SUM(b.GutStueck) AS MENGE
FROM OEE_StckList AS a
JOIN OEE_Produktion AS b on a.ArtNr = b.ArtNr
WHERE Year(b.Datum) = 2007
ORDER BY a.ArtNr

[uwewo]

Danke Norman,

so hatte ich es auch schon versucht, allerdings erhalte ich auf diese Weise immer nur den ersten Datensatz in der Stückliste. :cry:

[NormanNG]

Hi,

sorry, das GROUP BY fehlt natürlich

SQL-Code:

SELECT a.ArtNr,a.ArtBez, a.Menge, SUM(b.GutStueck) AS MENGE
FROM OEE_StckList AS a
JOIN OEE_Produktion AS b on a.ArtNr = b.ArtNr
WHERE Year(b.Datum) = 2007
GROUP BY a.ArtNr, a.ArtBez, a.Menge
ORDER BY a.ArtNr

[uwewo]

Das mit GRUOP wahr schon klar, ein summieren ohne GROUP ist sowieso nicht möglich.

Leider aber wie gesagt immer nur der erste Datensatz, der Stückliste.

[NormanNG]

Hi,

Zitat:

Das mit GRUOP wahr schon klar, ein summieren ohne GROUP ist sowieso nicht möglich.

Das ist - zumindest für MS SQL - nicht richtig.



Mit der Abfrage oben erhält man alle Zeilen aus Tabelle a, für die es mindestens eine entsprechende Zeile aus Tabelle b gibt, für die gilt: a.ArtNr=B.ArtNr und (B.Datum) in 2007. Bist du sicher, das in Tabelle b mehr Daten liegen, als ausgegeben werden?

Versuchs doch mal mit

SQL-Code:

SELECT a.ArtNr,a.ArtBez, a.Menge, Count(b.*) as ANZAHL
FROM OEE_StckList AS a
LEFT JOIN OEE_Produktion AS b on a.ArtNr = b.ArtNr and Year(b.Datum) = 2007
GROUP BY a.ArtNr, a.ArtBez, a.Menge
ORDER BY a.ArtNr

Damit bekommst du die ANZAHL der aus b passenden Zeile je Artikel...

[uwewo]

Halo Norman,

Danke nochmal für Deinen tatkräftigen Einsatz.

Nun zum Stand der Dinge

SQL-Code:

SELECT a.ArtNr,a.ArtBez, a.Menge, Count(b.ArtNr) as ANZAHL, Sum(b.GutStueck) as Menge 
FROM OEE_StckList AS a 
LEFT JOIN OEE_Produktion AS b 
ON a.ArtNr = b.ArtNr and Year(b.Datum) = 2007 
GROUP BY a.ArtNr, a.ArtBez, a.Menge 
ORDER BY a.ArtNr

Count gibt bei der Anzahl immer 2 anstatt 1 zurück, dass wäre aber nicht schlimm denn
SUM(b.GutStueck) liefert die richtige Menge der produzierten Teile.

Allerdings wenn ich

SQL-Code:

(a.Menge * Sum(b.GutStueck)) as Gesamt

berechne fehlen zwischen 3+5 Artikel aus der Stückliste.

Das weitere und wahrscheinlich auch grössere Problem ist, dass ich die jetzt gruppierten Artikel nocheinmal gruppieren müsste damit die Artikel aus der Stückliste auch summiert werden.

Ich denke mal ich werde wohl nicht um eine weitere Abfrage, oder Temp Table herum kommen. :?

[omata]

Hallo uwewo,

hier mal mein Vorschlag...

SQL-Code:

SELECT p.ArtNr, s.ArtBez, s.Menge, s.MengenEinheit, s.stueck
FROM OEE_Produktion p
LEFT JOIN (SELECT ArtNr, SUM(b.GutStueck) stueck
           FROM OEE_StckList
           GROUP BY ArtNr) s
  ON p.ArtNr = s.ArtNr
WHERE p.ArtNr = '4030.012'
  AND YEAR(p.datum) = 2007
ORDER BY p.ArtNr

Ich würde dir auch gern konkreter helfen, aber ich muss zugeben, dass ich aus deinen Informationen einfach nicht schlau werden. Was steht wie in deinen Tabellen und was möchtest du konkret haben?
Das zusätzliche Gruppieren sollte auch kein Problem sein. Aber ich werde aus deinen Angaben einfach nicht schlach, schade.

Gruss
Thorsten

[uwewo]

Zitat:

Zitat von omata

Ich würde dir auch gern konkreter helfen, aber ich muss zugeben, dass ich aus deinen Informationen einfach nicht schlau werden. Was steht wie in deinen Tabellen und was möchtest du konkret haben?

Ok, hier nocheinmal zum besseren Verständnis die sehr vereinfachte Tabellen Übersicht, und mein gewünschtes Ergebnis.

Tabelle -> Produktion
ArtikelNr_Produkt, ArtikelBez_Produkt, Produzierte_Menge

Tabelle -> Stückliste
ArtikelNr_Produkt, ArtikelNr_Einzelteil, Menge_Einzeilteil

Jedes Produkt besteht aus einer Stückliste, die wiederum die Einzelteile eines Produkts enthält.

Die Tabelle Produktion enthält die Menge(in Stck) aller Produkte die produziertz wurden.
Die Stücklistentabelle besteht aus allen Einzelteilen die zu einem Produkt gehören, Mengenangabe(Stück, kg) etc.

z.B.
ProduktionTabelle
ArtikelNr_Produkt | ArtikelBez_Produkt | Produzierte_Menge
4030.012 | Test | 1200
6070.016 | Test2 | 600
4030.012 | Test | 200

produzierte Menge des Produkt 4030.012 = 1400
produzierte Menge des Produkt 6070.016 = 600

StücklistenTabelle
ArtikelNr_Produkt | ArtikelNr_Einzelteil | Menge_Einzeilteil
4030.012 | 1000 | 0.5
4030.012 | 1001 | 0.5
4030.012 | 1002 | 1
6070.016 | 1000 | 1

somit besteht Produkt 4030.012 aus 3 Einzelteilen und zwar 1000, 1001, 1002 nun möchte ich die Gesamtmenge aller Einzelteile berechnen. Das wäre in diesem Fall:

für Produkt 4030.012 und Einzelteil-Nr. 1000 | 1400 Stück * 0.5 = 700
für Produkt 6070.016 und Einzelteil-Nr. 1000 | 600 Stück * 1 = 600
Gesamtmenge für Einzelteil-Nr. 1000 = 1300

Also wie gesagt ich möchte die Summe aller Einzelteile, von den Summen aller Produkte.
Wobei jedes Produkt teilweise aus unterschiedlichen Einzelteilen bestehen kann.

[omata]

Ok, so verstehe ich was du haben möchtest.

Hier mein Vorschlag...

SQL-Code:

SELECT teilnr, SUM(multiplikant) summe
FROM (SELECT teilnr, x.menge * y.menge AS multiplikant
      FROM (SELECT artnr, SUM(menge) menge
            FROM oee_produktion
            GROUP BY artnr) x
      INNER JOIN oee_stcklist y
        ON x.artnr = y.artnr) x
GROUP BY teilnr

Edit: ich habe das mit MySQL 5 getestet. Ob das auch schon mit 4.11 geht weiss ich nicht.

Gruss
Thorsten

[uwewo]

Hallo Thorsten,

wenn ich das ganze richtig verstehe, vergibst Du dem SubSelect einen Namen in diesem Fall "x" bzw. "y", nette Idee wusste nicht das dies überhaupt möglich ist.

Wenn ich mir die Selects von innen nach aussen anschaue, verstehe ich das ganze sogar :lol:
Leider erhalte ich immer wieder die gleiche Fehlermeldung, "Unknown column x.artnr in on clause"
egal wie rum ich es versuche.

SQL-Code:

ON x.ArtikelNr = y.ArtikelNr) x

SQL-Code:

ON y.ArtikelNr = x.ArtikelNr) x

In jedem Fall ist x.artnr der Fehler. Desweiteren verstehe ich nicht ganz, dass sowohl beim Subselect

SQL-Code:

FROM (SELECT SUM(GutStueck) as menge 
            FROM tagesproduktion 
            GROUP BY ArtikelNr) x

sowie auch beim

SQL-Code:

ON x.ArtikelNr = y.ArtikelNr) x

ein x am Ende steht. Vielleicht kann mir das ja jemand erklären.

[omata]

Hallo uwewo,

ok ich versuche mal etwas Klarheit hier reinzubringen.

Wenn man schreibt...

SQL-Code:

SELECT x.spalte
FROM tabelle x

dann gibt man der tabelle einen Prefix (x) über den man dann auf die Spalten der Tabelle zugreifen kann.
Man könnte auch den Prefix weglassen und einfach den vollständigen Tabellennamen schreiben.

SQL-Code:

SELECT tabelle.spalte
FROM tabelle

Das ist mir allerdings zu umständlich.

Diese Prefix bzw. Tabellenangabe beim Spaltennamen ist allerdings nur dann nötig wenn man mehrere Tabellen auf einer Ebene hat, die die selben Spaltennamen haben. Damit man eben die Spalte die man meint eindeutig angeben kann.

Wenn man jetzt eine Unterabfrage anstatt einer realen Tabelle nimmt, verhält es sich ähnlich. Ich muss auf den Dateninhalt zugreifen können. Bei einer realen Tabelle gibt es schon einen Namen (den Tabellennamen) diesen kann ich auch durch einen Prefix ersetzen. Bei einer Unterabfrage ist diese Namenvergabe ein muss, da die Unterabfrage natürlich keinen Namen hat. Deshalb muss hier immer ein Prefix angegeben werden.

SQL-Code:

SELECT *
FROM (SELECT *
      FROM tabelle) x

Das X nehme ich meist aus reiner Faulheit.

Schauen wir uns jetzt die innerste Abfrage an...

SQL-Code:

SELECT artnr, SUM(menge) menge
FROM oee_produktion
GROUP BY artnr

da in deiner Produkt-Tabelle ein Produkt mehrfach vorkommen kann (nur mal nebenbei: das DB-Design ist nicht gerade elegant) fasse ich hier alle Artikel zusammen und bilde aus den Einzelsummen eine Gesamtsumme pro Artikel.

Dieses Ergebnis nehme ich und verarbeite es weiter...

SQL-Code:

SELECT teilnr, x.menge * y.menge AS multiplikant
FROM (SELECT artnr, SUM(menge) menge
      FROM oee_produktion
      GROUP BY artnr) x
INNER JOIN oee_stcklist y
  ON x.artnr = y.artnr

hier wird jetzt die Stückliste zu jedem Artikel hinzugejoint und Mengen multipliziert.
Aus Faulheit habe ich meine beiden Ergebnismengen X und Y genannt.

Dieses Ergebnis nehme ich und verarbeite es wieder weiter...

SQL-Code:

SELECT teilnr, SUM(multiplikant) summe
FROM (SELECT teilnr, x.menge * y.menge AS multiplikant
      FROM (SELECT artnr, SUM(menge) menge
            FROM oee_produktion
            GROUP BY artnr) x
      INNER JOIN oee_stcklist y
        ON x.artnr = y.artnr) x
GROUP BY teilnr

da ich die oben erstellte Abfrage in die neue einbette kann ich einfach wieder den gleichen Prefix nehmen, weil es eine neue Ebene ist. Auf einer Ebene kann natürlich jeder Prefix nur einmal vorkommen. Da ich hier aber eine neue Ebene aufmache kann ich einfach wieder mit X weiter machen. Man kann auch jeden anderen Prefix nehmen. Also zum Beispiel so...

SQL-Code:

SELECT teilnr, SUM(multiplikant) summe
FROM (SELECT teilnr, x.menge * y.menge AS multiplikant
      FROM (SELECT artnr, SUM(menge) menge
            FROM oee_produktion
            GROUP BY artnr) x
      INNER JOIN oee_stcklist y
        ON x.artnr = y.artnr) z
GROUP BY teilnr

Das Kind muss nur einen Namen bekommen.

Ich hoffe ich konnte dir das verständlich rüberbringen.

Deine Fehlermeldung sagt einfach nur aus, das die Spalte nicht vorhanden ist. Die Spalten heissen vielleicht etwas anders in deinen Tabellen, das must du dann anpassen.

Probier einfach die einzelnen Abfragen aus und baue sie dann langsam wieder zusammen.

Hoffe das hilft dir weiter.

Gruss
Thorsten

[uwewo]

Hallo Thorsten,

bis auf Dein lettzes "x" hatte ich auch alles vorher verstanden.
Natürlich habe ich auch jedes SELECT einzeln ausprobiert und auch das mit der Namensgebung, wegen doppelt vorhandener Feldbezeichnungen in verschiedenen Tabellen war mir klar.

Das man Subselects auf diese Weise Alias-Namen zuweisen kann, war mir allerdings neu und es freut mich immer wieder etwas neues zu lernen.

Allerdings bin ich mir zu 100% sicher das die Feldnamen meiner Query, mit denen in den Tabellen übereinstimmen daher mein letztes Posting.

Na ja wird wohl doch an MySQL 4.11 liegen.

Ich DANKE Dir aber trotzdem für die ganze Mühe die Du dir gemacht hast.

Ausserdem habe ich auch wieder etwas dazu gelernt, was man nicht unterschätzen sollte.

Schönes Wochenende