SELECT mit SubSELECT und GROUP BY

[c_u_b_e]

SELECT mit SubSELECT und GROUP BY

Hallo, folgende Abfrage klappt:

SELECT max(t.Fwoche) as 'FWoche',
 max(k.Tourenkz) as Tour,
 sum(t.stueck) as Stueck,
 sum(t.einheiten) as Einheiten

 FROM P\_Technik t, A\_Kopf k

 WHERE k.id = t.id\_a\_kopf
 AND left(t.Fwoche,2) = '12'
 AND t.fsystem LIKE '%'
 AND t.p\_art 3
 AND right(t.Fwoche,2) \>= 10
 AND right(t.Fwoche,2) 

Wenn ich jetzt allerdings den SELECT - Bereich erweitere klappt es nicht mehr:


    
    SELECT max(t.Fwoche) as 'FWoche',
     max(k.Tourenkz) as Tour,
     sum(t.stueck) as Stueck,
     sum(t.einheiten) as Einheiten,
     sum(SELECT count(e.Typ)
     FROM P\_Einzelholz e
     WHERE k.id = e.id\_a\_kopf
     AND e.position = t.position
     AND e.Typ LIKE 'R%') as 'Rahmen'




Von diesen 'Unter-Selects' bräuchte ich allerdings noch mehr, auch auf andere Tabellen. Wenn ich das ganze ohne GROUP BY mache klappt es auch. Auch mit weiteren 'Unter-Selects'.

Was mache ich falsch?
Wie setzt man solche Abfrage eigentlich 'richtig' um?

Ich habe lange gesucht, aber nichts gefunden was mich weitergebracht hat.

Danke schonmal an alle!!

mfg c-u-b-e

[drambeldier]

Moin, cube,

schau Dir mal die from-Klauseln an:

FROM P_Technik t, A_Kopf k

und

FROM P_Einzelholz e

Fällt Dir was auf?

Wie setzt man solche Abfrage eigentlich ‚richtig‘ um?

Keine Ahnung, ich verstehe nicht, was Du vorhast, mir fehlt da die fachliche Beschreibung.

Gruß Ralf

[c_u_b_e]

Wir bauen Fenster und ich will eine Abfrage erstellen, in der ich eine Übersicht über Teile / Einheiten je Fertigungswoche (Fwoche) und Tag (Tourenkz) habe.

Eingrenzung von Woche, Tag, Fenstersystem

FROM P\_Technik t, A\_Kopf k

 WHERE k.id = t.id\_a\_kopf
 AND left(t.Fwoche,2) = '12'
 AND t.fsystem LIKE '%'
 AND t.p\_art 3
 AND right(t.Fwoche,2) \>= 10
 AND right(t.Fwoche,2) 

Im SELECT-Bereich will ich dann auf die einzelnen Tabellen für Scheibe, Rollläden, Stäbe, etc gehen. Je nach Auftragsnummer und Position. Von den folgenden Codezeilen möchte ich dann mehrere erstellen um die verschiedenen Bereich abzudecken. Und wie ich unsere Fertigungsleiter kenne, kommen da noch weitere hinzu.


    
    sum(SELECT count(e.Typ)
     FROM P\_Einzelholz e
     WHERE k.id = e.id\_a\_kopf
     AND e.position = t.position
     AND e.Typ LIKE 'R%') as 'Rahmen'




Resultat sollte dann ungefähr so aussehen:

Woche | Tag | Einheiten | Stück Scheiben | Stück Rollladen | Stück Rahmen

Danke nochmals!!

mfg c-u-b-e

[SomeOne]

Datenmodell bitte
Servus c-u-b-e

anscheinend werde nicht nur ich aus deinen Angaben nicht recht schlau.

Gib doch bitte mal bekannt , welche Tabellen du hast, welche Felder die haben, welchen Sinn diese Felder haben, wie die Tabellen zusammen hängen
und schliesslich: welches Ergebnis hätten wir denn gern ?

Es gibt also einen Kopf und dazu Details, 1:n, und zu den Details Materialien, auch 1:n … oder wie jetzt ?

Ohne diese Angaben wird dir kaum jemand eine sinnvolle Hilfe leisten können.

Gruss,
SomeOne

[drambeldier]

Ich auch, ich auch ;.)
Einfachste Darstellung wäre

Tabelle1 (Key, Fs_Attr1, FS_Attr2, …, Attr1, Attr2, Attr3)

Bitte nur die Attribute, die für Verknüpfung, Group und Summen gebraucht werden, sonst wird’s zu verwirrend!

Gruß Ralf

[c_u_b_e]

Tabellen
Ihr habt ja recht…

Ich versuche mal die Situation besser zu verdeutlichen:

A_Kopf a (ID, AufNr, Tour, …)
(Kopfdaten zum Auftrag)

P_Technik t (ID_aus_Kopf, AufPos, Fertigungswoche, Stueck, Einheiten, …)
Stueck = Stück einer Position

Aus der Tabelle P_Technik lese ich die Fertigungswoche und -jahr aus.
Welche Liefertour ein Auftrag hat steht in der Tabelle A_Kopf.
Gruppiert werden soll über Fertigungswoche und Tour.
Verknüpft sind alle Tabellen über ID_aus_Kopf und AufPos (Auftragsposition).

In den folgenden Tabellen stehen die einzelnen Informationen die dann pro Fertigungswoche und Tour aufsummiert werden sollen:

P_Einzelholz e (ID_aus_Kopf, AufPos, Rahmenteile, Fluegelteile, Sprossen, …)
Beispiel: sum(e.Rahmenteile * t.stueck)

P_Artikel ar (ID_aus_Kopf, AufPos, Artikelklasse, ArtikelNr, …)
Beispiel: Wenn ar.ArtikelNr = ‚2558-66652‘ dann Anzahl dieser Artikel pro Position * t.stueck // as Rolladen oder // as Handhebel

P_FluegelFeld ff (ID_aus_Kopf, AufPos, Oeffnungsart, …)
Beispiel: Wenn ff.Oeffnungsart LIKE ‚Fe0-000‘ dann Anzahl dieser Öffnungsarten pro Position * t.stueck

P_Teil te (ID_aus_Kopf, AufPos, InnenFarbe, Aussenfarbe, …)
Beispiel: Anzahl der Elemente, bei denen die InnenFarbe Außenfarbe ist

…und noch weitere Tabellen…

Die Ergebnistabelle (Gruppiert nach Fertigungswoche, Tour):
Fertigungswoche | Tour | Anzahl Fenster | Anzahl Einheiten | Rahmenteile | Flügelteile | Stück Rollladen | Stück Handhebel | Öffnungsart Standard | Öffnungsart WK2 | ZweiFarbig

Hoffentlich konnte ich damit mein Problem besser darstellen!!

Vielen DANK!!

mfg c-u-b-e

[SomeOne]

Servus c-u-b-e,

schon etwas besser , zumindestens hab ich jetzt eine Ahnung was du machen willst:

Du hast also den Klassiker, eine Kopftabelle, eine Positionstabelle,
dazu noch mehrere abhängige Tabellen die aber im gleichen Verhältnis
stehen wie die Positionstabelle … so weit so gut.

Und jetzt willst du aus den jeweiligen abhängigen Tabellen Summen und Anzahlen holen, auch gut.

Was du hier brauchst sind sog. „korrelierte Subqueries“, denn wenn du
erst mit LEFT JOINS über die Kopf- und Positionswerte die abhängigen
Tabellen anbindest und dann Summen/Anzahlen bildest, läuft die Abfrage zwar, liefert aber völligen Blödsinn ab (SQL JOINed erst und summiert dann, d.h. die Summe würde mit der Anzahl der JOIN-Treffer
multipliziert).

Du brauchst grundsätzlich etwas wie dieses hier, um die Stückzahl pro Woche zu summieren:

SELECT kopf.kopfid, position.woche, sum(position.stueckzahl)
FROM kopf
LEFT JOIN position ON position.kopfid = kopf.kopfid
GROUP BY kopf.kopfid, position.woche
;

Um zusätzlich die Summe aus Tabelle1 und die Summe aus Tabelle2 und die Anzahl aus Tabelle3 zu ermitteln, brauchst du etwas wie dieses hier:

SELECT__k.kopfid, p.woche, sum(p.stueckzahl),
________(SELECT sum(mengenfeld)
________ FROM tabelle1 AS t1
________ WHERE t1.kopfid = k.kopfid
________ AND t1.position = p.positionsnummer) AS tabwert1
________ ,
________(SELECT sum(mengenfeld)
________ FROM tabelle2 AS t2
________ WHERE t2.kopfid = k.kopfid
________ AND t2.position = p.positionsnummer) AS tabwert2
FROM____kopf AS k
LEFT____JOIN position AS p ON p.kopfid = k.kopfid
GROUP___BY k.kopfid, p.woche
;

Die „_“ dienen hier nur zur Formatierung, sonst kannst du das Statement kaum lesen …

Wie du in der Select-Zeile siehst, werden einerseits Spalten ausgelesen, anderseits bilden jedoch komplette eigene SELECT-Statements weitere Ausgabespalten … dies sind also Unterabfragen,
die die Daten aus der Hauptabfrage nutzen.

Hilft dir das Konzept weiter ?

@drambeldier: kannst du diesen Vorschlag unterstützen, oder habe ich
das Aufgabenstellung immer noch nicht erfasst ?

Gruss,
SomeOne

[drambeldier]

Hi cube,

das ist keine Datenbank, sondern eine als Datenbank verkleidete Excel-Tabelle. Bevor wir jetzt anfangen zu modellieren, nehmen wir es so hin und versuchen das Beste draus zu machen

Zu jeder Position gibt es beliebig viele - weiß nicht, wie ich das nennen soll, von jeder Sorte aber nur eine einzige (Holz, Artikel, Teil, Einheit, …).

Bau eine View, die genau diese Einzelteile nebeneinander stellt. Wird nicht ganz einfach, weil vermutlich nie alle gleichzeitig da sein müssen, geht aber. Auf diese View lässt Du dann die Zählungen los. Das Problem mit der Gruppierung (Du hast übrigens nicht gesagt, welcher Art das ist) sollte sich damit in Wohlgefallen auflösen.

Mit dem Zerlegen in 2 Schritte hast Du ein wenig von der Komplexität herausgenommen. Wenn die Geschichte mal läuft, kannst Du das Datenmodell immer noch verbessern. Oder auch nicht ))

Gruß Ralf

[c_u_b_e]

Hallo SomeOne,

Danke erstmal!!
Das werde ich heute mal ausprobieren!!

mfg c-u-b-e

[c_u_b_e]

Hi Ralf,

für diese Art von Datenbank kann ich nichts. wir sind auch nur Kunde…

Ich melde mich wieder, wenns klappt oder ich wieder nicht weiterkomme.

mfg cube

[c_u_b_e]

Hallo SomeOne,

folgendes klappt:

SELECT right(t.Fwoche,2) as 'FWoche', 
 k.Tourenkz as Tour,
 sum(t.stueck) as Stueck, 
 sum(t.einheiten \* t.stueck) as Einheiten

FROM A\_Kopf k
LEFT JOIN P\_Technik t ON k.id = t.id\_a\_kopf

WHERE left(t.Fwoche,2) = '12'
AND ( t.fsystem LIKE '068\_%' )
AND t.p\_art 3
AND right(t.Fwoche,2) \>= '10'
AND right(t.Fwoche,2) 

Wenn ich jetzt allerdings ein SELECT einfüge gibt es eine Fehlermeldung.
Hier das 'neue' SQL-Statement:


    
    SELECT right(t.Fwoche,2) as 'FWoche', 
     k.Tourenkz as Tour,
     sum(t.stueck) as Stueck, 
     sum(t.einheiten \* t.stueck) as Einheiten,
    **(SELECT sum(CASE WHEN te.Kennung = 1 OR te.Kennung = 2 THEN 1 END)   
     FROM p\_teil te   
     WHERE te.id\_a\_kopf = k.id  
     AND te.position = t.position  
     AND te.oberfli '(standard)'  
     AND te.oberfl te.oberfli) as 'ZweiFarbig'**   
    
    FROM A\_Kopf k
    LEFT JOIN P\_Technik t ON k.id = t.id\_a\_kopf
    WHERE left(t.Fwoche,2) = '12'
    AND ( t.fsystem LIKE '068\_%' )
    AND t.p\_art 3
    AND right(t.Fwoche,2) \>= '10'
    AND right(t.Fwoche,2) 
    
    ...und die Meldungen:
    1) Die 'A\_Kopf.ID'-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatefunktion nicht in der GROUP BY - Klausel enthalten ist.
    
    2) Die 'P\_Technik.Position'-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatefunktion nicht in der GROUP BY - Klausel enthalten ist.
    
    
    Da muss ich dann doch den Vorschlag von Ralf (über eine VIEW) gehen?
    Oder gibt es noch andere Möglichkeiten beim SQL-Statement?
    
    Danke nochmal
    
    mfg cube

[drambeldier]

Moin, cube,

SELECT right(t.Fwoche,2) as ‚FWoche‘,
k.Tourenkz as Tour,
sum(t.stueck) as Stueck,
sum(t.einheiten * t.stueck) as Einheiten,
(SELECT sum(CASE WHEN te.Kennung = 1 OR te.Kennung = 2 THEN 1
END)
FROM p_teil te
WHERE te.id_a_kopf = k.id
AND te.position = t.position
AND te.oberfli ‚(standard)‘
AND te.oberfl te.oberfli) as 'ZweiFarbig’

hier fehlt die schließende Klammer zum Subselect. Ist die beim Kopieren verloren gegangen?

Gruß Ralf

[c_u_b_e]

Hi Ralf,

ich öffne insgesamt 2 Klammern und schließe diese doch auch wieder

(SELECT sum(CASE … THEN 1 END) … te.oberfli)

Oder habe ich was grundsätzlich falsch gemacht, was ich grade nicht verstehe?

Danke nochmals!

mfg cube

[SomeOne]

SELECT right(t.Fwoche,2) as ‚FWoche‘,
k.Tourenkz as Tour,
sum(t.stueck) as Stueck,
sum(t.einheiten * t.stueck) as Einheiten,
(SELECT sum(CASE WHEN te.Kennung = 1 OR te.Kennung = 2 THEN 1
END)
FROM p_teil te
WHERE te.id_a_kopf = k.id
AND te.position = t.position
AND te.oberfli ‚(standard)‘
AND te.oberfl te.oberfli) as 'ZweiFarbig’

FROM A_Kopf k
LEFT JOIN P_Technik t ON k.id = t.id_a_kopf
WHERE left(t.Fwoche,2) = ‚12‘
AND ( t.fsystem LIKE ‚068_%‘ )
AND t.p_art 3
AND right(t.Fwoche,2) >= ‚10‘
AND right(t.Fwoche,2)

Servus cube,

also langsam kapiere ich gar nix mehr:

dein Statement wie es oben steht ist soeben fehlerfrei durch die Syntaxprüfung auf einer AS/400 mit DB2 gelaufen.

Die Fehlermeldung versteh ich auch nicht

…und die Meldungen:

1. Die ‚A_Kopf.ID‘-Spalte ist in der Auswahlliste ungültig, da
   sie nicht in einer Aggregatefunktion nicht in der GROUP BY -
   Klausel enthalten ist.

2. Die ‚P_Technik.Position‘-Spalte ist in der Auswahlliste
   ungültig, da sie nicht in einer Aggregatefunktion nicht in der
   GROUP BY - Klausel enthalten ist.

diese Felder stehen doch in überhaupt keiner Auswahlliste (SELECT …) !?

Offensichtlich versteht dein Tool nur genau die Korrelation nicht wie ich
fürchte …

Mehr als ein grundsätzlich inordnunges Statement kann ich dazu wohl nicht mehr beitragen, EXCEL/ACCESS-Profis an die Front bitte !

Gruss,
SomeOne

[c_u_b_e]

Hi SomeOne,

es tut mir echt leid…

Ich habe mir eben noch Zugang über das Microsoft SQL Server Management Studio verschafft. Auch wenn ich die Abfrage direkt von dort starte, kommen die beiden Fehler. Es ist übrigens ein SQL-2008-Server; das hatte ich vergessen mitzuteilen, sorry.

Wenn ich auf ‚Analysieren‘ klicke kommt auch die Meldung:
"Befehl(e) wurde(n) erfolgreich ausgeführt.

Der Fehler kommt, sobald ich die Abfrage starte.
Das Einzige was mir dort mehr ausgespuckt wurde ist, dass es sich um folgende beiden Zeilen im Subselect dreht:

WHERE te.id\_a\_kopf = k.id
AND te.position = t.position

Mit diesen beiden Zeilen beziehe ich mich auf das FROM von der Hauptabfrage. Hat es damit vielleicht was zu tun?

Ich seniere noch ein wenig über der Sache.

Danke

mfg cube

[drambeldier]

Hi cube,

Oder habe ich was grundsätzlich falsch gemacht, was ich grade
nicht verstehe?

nein, ich brauch ne neue Brille

Gruß Ralf

[SomeOne]

Hi cube,

das bestätigt nur meinen Verdacht, dass dein Tool die Korrelation nicht erkennt.
Offensichtlich tritt der Fehler auf, wenn die Hauptabfrage Aggregatfunktionen enthält und dazu noch eine korrelierte Unterabfrage.
Da geht wohl die Syntaxprüfung hin und guckt erst mal ob alle Spalten denn auch in der GROUP Klausel vorkommen … auch die korrelierte ! Shit !

Wie ist denn die Möglichkeit zuerst mit der Unterabfrage eine Ausgabedatei zu schreiben und die dann zur Hauptabfrage hinzu zu JOINen ?
Nicht hübsch, aber wenn es hilft …

Ansonsten: MS SQL Server Sachverständige vor bitte !
Hier ist für mich Ende von Latein …

Gruss,
SomeOne