Group by Fehler (kann nicht zusammenfassen)

[laines]

Hallo

Ich habe eine MSSQL Abfrage aus 2 Tabellen hingebracht.
Er zieht mir aus einer Adressdatenbank und den Rechnungen.
Er soll den Kunden nur 1x Auflisten und das tut er nicht

Habe schon GROUP BY versucht aber ich bekomme den Fehler:

siehe Bild

Code: Alles auswählen

SELECT t1.ckundenNr, t1.cFirma, t1.cAnrede, t1.cVorname, t1.cName, t1.cStrasse, t1.cPLZ, t1.cOrt, t2.kRechnung, t2.tKunde_kKunde
FROM tkunde t1, trechnung t2
WHERE t1.ckundenNr = t2.tKunde_kKunde
GROUP BY t1.ckundenNr

Schon mal Danke und Gruß
Laines

[Hitman]

GROUP BY ist nur im Zusammenspiel mit Aggregatfunktionen sinnvoll - daher auch der Fehler, der sagt, dass alle selektierten Felder Teil der GROUP BY Klausel sein müssen.
Stell dir mal vor, du hast für eine Kundennummer mehrere Adressen. Wie soll die Datenbank bei dem Query dann damit umgehen? Laut GROUP BY dürfte es ja nur eine Kundennummer geben, aber es gibt dazu mehrere Straßen, Orte, etc., also ist das DBMS gezwungen dir mehrere Zeilen auszugeben, und das würde ja einen Vertoß gegen GROUP BY bedeuten --> Dilemma!

Was du eigentlich willst, ist doch sicher was anderes. Um rauszukriegen was, brauch ich aber mehr Details. Dein jetziges Anliegen ergibt nämlich nicht wirklich sinn: du hast doch sicher pro Kunde mehrere Rechnungen, wie soll das DBMS dir also Rechnungen und Kunden aggregieren und dir dennoch die Adresse nur einmal ausgeben? Das würde als zwei getrennte Abfragen mehr Sinn machen: erstmal Kundendatensätze abrufen, und dann zu jedem Kunden getrennt die Rechnungen abrufen.

[Socke]

Code: Alles auswählen

FROM tkunde t1, trechnung t2

Deine Abfrage ergibt ein kartesisches Produkt (alle Eintrage aus tkunde kombiniert mit allen Einträgen aus trechnung), und das ist ganz sicher nicht das, was du haben willst. Ein INNER JOIN ist wohl bei weitem besser geeignet (wenn unbekannt, bitte selbst nachlesen):

Code: Alles auswählen

FROM tkunde t1 INNER JOIN trechnung t2 ON (t1.ckundenNr = t2.tKunde_kKunde)

Dann hab ich noch zwei Fragen/Hinweise...

Erste Frage:

Code: Alles auswählen

SELECT t1.ckundenNr, [...], t2.tKunde_kKunde
FROM tkunde t1, trechnung t2
WHERE t1.ckundenNr = t2.tKunde_kKunde

Dein Ergebnis enthält tkunde.ckundenNr und trechung.tKunde_kKunde; beide Felder sind per Defintion (WHERE) gleich. Lass eines von beiden weg, du wirst es nie brauchen.

Zweite Frage:

Er zieht mir aus einer Adressdatenbank und den Rechnungen.
Er soll den Kunden nur 1x Auflisten und das tut er nicht

Willst du einfach nur eine Auflistung aller Kunden, die mindestens eine Rechnung haben?
Bei deiner Abfrage werden alle Rechnungen mit den entsprechenden Kundeninformationen verknüpft. Ein GROUP BY kann dir da auch nicht weiter helfen, weil mehrere verschiedene Rechnungen eines Kunden das Gruppieren verhindern. (Lösung: Rechnungen nicht abfragen)

P.S.: Willkommen im Forum

[laines]

Hallo

Danke für die Antwort.
Das ganze Soll später eine Statistik ergeben.
z.B.

KD.NR | Firma | Name | usw... | Umsatz 2010 | Umsatz 2011 | ...
1000 | xyz | abc | .... | 20.000 € | 40.000 € | ...

oder man klickt den Kunden an. ...

Ich war ja schon froh eine Abfrage hin zu bekommen wie soll ich denn da zwei hinkriegen und die auch noch verknüpfen ??

[Socke]

Ich war ja schon froh eine Abfrage hin zu bekommen wie soll ich denn da zwei hinkriegen und die auch noch verknüpfen ??

Wenn du Access hast, kannst du damit wunderbar üben und die Abfragen grafisch mit der Maus zusammenklicken. Dazu solltest du dir auf jeden Fall noch weitere Literatur zum Thema SQL anlesen (zum Beispiel bei wikibooks.org).

Das ganze Soll später eine Statistik ergeben.
z.B.

KD.NR | Firma | Name | usw... | Umsatz 2010 | Umsatz 2011 | ...
1000 | xyz | abc | .... | 20.000 € | 40.000 € | ...

Wie sieht denn das Tabellenlayout aus? Versuche einmal auf Basis der Tabellen ganz frei, aber möglichst genau, zu formulieren, was denn in der Abfrage hinaus kommen soll, beispielsweise:
Ich möchte alle Kunden mit ihren Daten (Kunden-Nummer, Firma, Name, Adresse, [...]) aus der Tabelle tkunde auflisten. Dazu soll für die Jahre 2010, 2011 und 2012 der gesamte Jahresumsatz eines jeden Kunden ermittelt werden. Den Jahresumsatz erhalte ich, indem ich die Beträge aller Rechnungen eines Kunden (Kundennummer) in einem Jahr zusammenadiere. Die Jahre kann ich anhand [...] unterscheiden. und sow weiter.

[laines]

Hay

Du bist echt COOL Ich habe die Abfrage schon mal dahingehend hin bekommen, dass er mir alle Kunden mit Betrag ausgibt.

Code: Alles auswählen

SELECT t1.ckundenNr, t1.cFirma, t1.cAnrede, t1.cVorname, t1.cName, t1.cStrasse, t1.cPLZ, t1.cOrt,
t2.tBestellung_kBestellung, t2.tKunde_kKunde, t3.dDatum, t3.fBetrag, t3.kBestellung
FROM tkunde t1, trechnung t2, tZahlung t3
WHERE t1.ckundenNr =  t2.tKunde_kKunde
AND t2.tBestellung_kBestellung = t3.kBestellung

Kann ich das nun in einer einzigen Abfrage summieren ??

Ich sollte aufschreiben ...

KD-Nr., Firma, Anrede, Vorname, Nachname, Strasse, PLZ und Ort erscheinen, diese wird aus der Tabelle tkunde aus den Feldern cKundenNr, cFirma, cAnrede, cVorname, cName, cStrasse, cPLZ, cOrt geholt.
Anschließend sollen die bezahlten Rechnungen, addiert nach einer Auswahl in einem DropDown Feld entweder gesamt oder Jahr xxxx dem Kunden zugewiesen werden.
Diese Daten bekomme ich aus den Tabellen trechnung und tZahlung.
Eindeutige Verbindungen sind:
tkunde → cKundenNr = trechnung → tKunde_kKunde
trechnung → tBestellung_kBestellung = tZahlung → kBestellung

Hilft das weiter ??

[Socke]

Anschließend sollen die bezahlten Rechnungen, addiert nach einer Auswahl in einem DropDown Feld entweder gesamt oder Jahr xxxx dem Kunden zugewiesen werden.

Hierfür brauchst du definitv zwei Abfragen. Je nach dem, was ausgewählt ist, rufst du die eine oder die andere auf.

Ich habe mir erlaubt, die Tabellen in Access zu modellieren:

Eine mögliche Modellierung der Daten in Access.

layout_und_beziehungen.png (9.31 KiB) 3539 mal betrachtet

Wenn du kein Access hast, solltest du dir die Tabellenstrukur und die Beziehungen unter den Tabellen irgendwie grafisch aufmalen. Ein gutes Werkzeug dazu ist zum Beispiel der Diagramm-Editor Dia.

Wenn das geschehen ist, kann man auch die Daten ganz bequem eingeben:

Dateneingabe mit der angegebenen Modellierung

Bei den Abfragen muss ich selbst noch was knobeln. Aber du solltest dir auf jeden Fall schonmal die JOINS anlesen und ausprobieren -- ohne die gehts nämlich nicht.

[DiBo33]

Was du suchst nennt man pivotieren/Kreuztabelle

Code: Alles auswählen

SELECT tk.ckundenNr, ...
    , YEAR(tz.dDatum) Jahr
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2010 THEN tz.fBetrag ELSE 0 END) `2010`
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2011 THEN tz.fBetrag ELSE 0 END) `2011`
FROM tkunde tk
JOIN trechnung tr
    ON tk.ckundenNr = tr.tKunde_kKunde
JOIN tZahlung tz
    ON tr.tBestellung_kBestellung = tz.kBestellung
GROUP BY tk.ckundenNr, ..., Jahr
ORDER BY tk.ckundenNr, Jahr

Wobei du die Jahre vorher ermitteln und das Statement dynamisch zusammen bauen kannst.

Beim GROUP BY müssen alle Felder welche nicht einer Aggregatfunktion entstammen aufgelistet sein.

[laines]

Hallo DiBo33

Vielen Dank, das hat mir schon sehr weiter geholfen.
Summieren tut er nicht, somit ich bekomme es einfach nicht hin, dass er nur 1x meinen Kunden anzeigt.

Ich habe nun schon alle erdenklichen Variationen in GROUP BY ausprobiert aber er Gruppiert mir das Jahr nicht

Code: Alles auswählen

SELECT tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt,
tr.tBestellung_kBestellung, tr.tKunde_kKunde,
tz.kBestellung, YEAR (tz.dDatum) Jahr
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2005 THEN tz.fBetrag ELSE 0 END) '2005'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2006 THEN tz.fBetrag ELSE 0 END) '2006'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2007 THEN tz.fBetrag ELSE 0 END) '2007'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2008 THEN tz.fBetrag ELSE 0 END) '2008'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2009 THEN tz.fBetrag ELSE 0 END) '2009'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2010 THEN tz.fBetrag ELSE 0 END) '2010'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2011 THEN tz.fBetrag ELSE 0 END) '2011'
FROM tkunde tk
INNER JOIN trechnung tr
    ON tk.ckundenNr = tr.tKunde_kKunde
INNER JOIN tZahlung tz
    ON tr.tBestellung_kBestellung = tz.kBestellung
GROUP BY tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt,
tr.tBestellung_kBestellung, tr.tKunde_kKunde,
tz.fBetrag, tz.kBestellung, tz.dDatum, Jahr
ORDER BY tk.ckundenNr

Wenn ich Jahr in der GROUP-BY Klausel drin lasse bekomme ich die Fehlermeldung:
Ungültiger Spaltennahme 'Jahr'
Gebe ich alle Jahreszahlen also 2005, 2006, ... ein, Fehlermeldung:
Jeder GROUP-BY Klausel muss mindestens eine Spalte enthalten die kein äußerer Verweis ist.

Wo liegt mein Denkfehler ?

[Socke]

Wenn ich Jahr in der GROUP-BY Klausel drin lasse bekomme ich die Fehlermeldung:
Ungültiger Spaltennahme 'Jahr'

Alias Namen für Spalten werden anders angegeben als Alias-Namen für Tabellen:

Code: Alles auswählen

SELECT alias_fuer_tabelle.Spalte, tabelle.Spalte AS anderer_Spaltenname FROM tabelle alias_fuer_tabelle;

[laines]

Alias Namen für Spalten werden anders angegeben als Alias-Namen für Tabellen:

Das behebt aber nicht mein Problem, dass er mir meine Kunden NICHT (Jahres abhängig) summiert? oder doch.

[DiBo33]

Hallo DiBo33

Vielen Dank, das hat mir schon sehr weiter geholfen.
Summieren tut er nicht, somit ich bekomme es einfach nicht hin, dass er nur 1x meinen Kunden anzeigt.

Ich habe nun schon alle erdenklichen Variationen in GROUP BY ausprobiert aber er Gruppiert mir das Jahr nicht

Code: Alles auswählen

SELECT tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt,
tr.tBestellung_kBestellung, tr.tKunde_kKunde,
tz.kBestellung, YEAR (tz.dDatum) Jahr
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2005 THEN tz.fBetrag ELSE 0 END) '2005'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2006 THEN tz.fBetrag ELSE 0 END) '2006'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2007 THEN tz.fBetrag ELSE 0 END) '2007'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2008 THEN tz.fBetrag ELSE 0 END) '2008'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2009 THEN tz.fBetrag ELSE 0 END) '2009'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2010 THEN tz.fBetrag ELSE 0 END) '2010'
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2011 THEN tz.fBetrag ELSE 0 END) '2011'
FROM tkunde tk
INNER JOIN trechnung tr
    ON tk.ckundenNr = tr.tKunde_kKunde
INNER JOIN tZahlung tz
    ON tr.tBestellung_kBestellung = tz.kBestellung
GROUP BY tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt,
tr.tBestellung_kBestellung, tr.tKunde_kKunde,
tz.fBetrag, tz.kBestellung, tz.dDatum, Jahr
ORDER BY tk.ckundenNr

Wenn ich Jahr in der GROUP-BY Klausel drin lasse bekomme ich die Fehlermeldung:
Ungültiger Spaltennahme 'Jahr'
Gebe ich alle Jahreszahlen also 2005, 2006, ... ein, Fehlermeldung:
Jeder GROUP-BY Klausel muss mindestens eine Spalte enthalten die kein äußerer Verweis ist.

Wo liegt mein Denkfehler ?

Ich sehe gerade du hast TSQL (welche Version eigtl.?) und nicht MySQL, du musst die Aliasbezeichner anders angeben

Code: Alles auswählen

,SUM( CASE WHEN YEAR(tz.dDatum) = 2005 THEN tz.fBetrag ELSE 0 END) [2005]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2006 THEN tz.fBetrag ELSE 0 END) [2006]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2007 THEN tz.fBetrag ELSE 0 END) [2007}

Statt GROUP BY Jahr musst du GROUP BY YEAR(tz.dDatum) angeben, desweiteren darfst du keine Einzelbestellungen selektieren also nichts aus Bestellung und nichts aus Rechnung.

Code: Alles auswählen

SELECT tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt,
 YEAR (tz.dDatum) Jahr
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2005 THEN tz.fBetrag ELSE 0 END) [2005]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2006 THEN tz.fBetrag ELSE 0 END) [2006]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2007 THEN tz.fBetrag ELSE 0 END) [2007]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2008 THEN tz.fBetrag ELSE 0 END) [2008]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2009 THEN tz.fBetrag ELSE 0 END) [2009]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2010 THEN tz.fBetrag ELSE 0 END) [2010]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2011 THEN tz.fBetrag ELSE 0 END) [2011]
FROM tkunde tk
INNER JOIN trechnung tr
    ON tk.ckundenNr = tr.tKunde_kKunde
INNER JOIN tZahlung tz
    ON tr.tBestellung_kBestellung = tz.kBestellung
GROUP BY tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt,
YEAR(tz.dDatum)
ORDER BY tk.ckundenNr

Vglch. http://www.itrain.de/knowhow/sql/tsql/pivot/pivot.asp" onclick="window.open(this.href);return false;

[laines]

Hallo und DANKE ! DANKE ! DANKE

Das sind doch mal Hilfestellungen.
Jetzt muss ich nur noch herausfinden wie ich meinen Kunden in eine Zeile bekomme.

Aktuell:
Kd.Nr. | Firma | 2005 | 2006 | 2007 ...
10000 | xyz | 100 € | 0 € | 0 € ...
10000 | xyz | 0 € | 110 € | 0 € ...
10000 | xyz | 0 € | 0 € |150 € ...

P.s. Es ist eine MSSQL SERVER2008 Datenbank

[DiBo33]

Hallo und DANKE ! DANKE ! DANKE

Das sind doch mal Hilfestellungen.
Jetzt muss ich nur noch herausfinden wie ich meinen Kunden in eine Zeile bekomme.

Aktuell:
Kd.Nr. | Firma | 2005 | 2006 | 2007 ...
10000 | xyz | 100 € | 0 € | 0 € ...
10000 | xyz | 0 € | 110 € | 0 € ...
10000 | xyz | 0 € | 0 € |150 € ...

Sollte eigtl. nicht vorkommen, es sei denn du hast noch nach einem Feld gruppiert welches unterschiedliche Einträge hat.

Du könntest noch versuchen MAX auf die Einträge anzuwenden

Code: Alles auswählen

,MAX(SUM( CASE WHEN YEAR(tz.dDatum) = 2005 THEN tz.fBetrag ELSE 0 END)) [2005]

Edit: Ah, ich sehe gerade, du musst das YEAR(tz.dDatum) aus der SELECT und der GROUP BY Klausel entfernen

P.s. Es ist eine MSSQL SERVER2008 Datenbank

Noch besser, ab 2005 hat TSQL eine eingebaute Pivotfunktion, hieru musst du aber mal Google bemühen da ich nur bis 2000 mit TSQL gearbeitet habe.

[laines]

Hallo DiBo33

Nochmal Danke
Der Korrekte Code sieht so aus:

Code: Alles auswählen

SELECT tk.kKunde, tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt
    ,SUM( tz.fBetrag) AS [Gesamt]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2005 THEN tz.fBetrag ELSE 0 END) [2005]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2006 THEN tz.fBetrag ELSE 0 END) [2006]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2007 THEN tz.fBetrag ELSE 0 END) [2007]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2008 THEN tz.fBetrag ELSE 0 END) [2008]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2009 THEN tz.fBetrag ELSE 0 END) [2009]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2010 THEN tz.fBetrag ELSE 0 END) [2010]
    ,SUM( CASE WHEN YEAR(tz.dDatum) = 2011 THEN tz.fBetrag ELSE 0 END) [2011]
FROM tkunde tk
INNER JOIN trechnung tr
    ON tk.kKunde = tr.tKunde_kKunde
INNER JOIN tZahlung tz
    ON tr.tBestellung_kBestellung = tz.kBestellung
GROUP BY tk.kKunde, tk.ckundenNr, tk.cFirma, tk.cAnrede, tk.cVorname, tk.cName, tk.cStrasse, tk.cPLZ, tk.cOrt
ORDER BY 'Gesamt' DESC

Nun kann ich anfangen das ganze zu verfeinern.