SUM Funktion

[Der_Paul]

Hallo Leute,
hallo Robert

ich beiße mir gerade an der Syntax für eine Summen-Funktion die Zähne aus. Die Abfrage ohne Summe Funktion schaut wie folgt aus und funktioniert.

SELECT "Konto"."Buchungstag", "Konto"."Verwendungszweck", "Konto"."Termine_ID", "Konto"."Betrag", "Konto"."Kontakt1_ID", "Konto"."Kontakt2_ID", "Kontakte"."Name", "Kontakte"."Vorname" FROM { oj "Konto" LEFT OUTER JOIN "Kontakte" "Kontakte_1" ON "Konto"."Kontakt2_ID" = "Kontakte_1"."ID" }, "Kontakte" WHERE "Konto"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 ORDER BY "Kontakte"."Name" ASC

Darin geht es um die Zahlungen der Teilnehmergebühren einer Ferienfreizeit.
"Termin.ID" = 7 --- Das ist die diesjährige Ferienfreizeit.
In der Konto-Tabelle stehen sämtliche Buchungen. Dort wir die Zahlung neben dem Termin auch den Teilnehmern zugeordnet. Wobei manche Eltern mit einer Überweisung für beide Kinder bezahlen. Daher Kontakt1_ID und Kontakt2_ID.

Ich hätte jetzt gerne eine Abfrage die a) einzelnen Summen der Teilnehmer aufaddiert und b) diese Summe von "Termine"."Kosten" subtrahier, damit ich diese als Quelle für einen Serienbrief nutzen kann. Nach dem Motto: "Summe X wurde bisher bezahlt, somit muss noch Summe Y bezahlt werden.

Seid ihr so freundlich und helft mir auf den richtigen Weg.

Vielen Dank und viele Grüße
Paul

[RobertG]

Hallo Paul,

wenn Du die Serienbriefe über den Report-Builder auswirfst, dann könntest Du das da berechnen. So wird Dir aber zusammen mit der internen Datenbank nichts anderes übrig bleiben als das Ganze mit korrelierenden Unterabfragen zu gestalten. Es fehlt Dir so eine Funktion wie in MySQL die Funktion "WITH ROLLUP", die neben Einzelbeträgen anschließend die Summe der Einzelbeträge darstellt.

Ich kürze einmal Deinen Code etwas zusammen - vor allem bezüglich der Tabellenzuweisungen, die mir so nicht ganz klar sind. Dann schreibe ich den entsprechenden Code dazu:

Code: Alles auswählen

SELECT "Konto"."Betrag", "Kontakte"."Name" FROM "Konto" AS "a", "Kontakte" WHERE "Konto"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 ORDER BY "Kontakte"."Name" ASC

Code: Alles auswählen

SELECT "a"."Betrag", "Kontakte"."Name", (SELECT SUM ("Betrag") FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID) AS "Summe" FROM "Konto" AS "a", "Kontakte" WHERE "Konto"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 ORDER BY "Kontakte"."Name" ASC

Durch die korrelierende Unterabfrage wird zu jedem Datensatz ausgerechnet, was die betreffenede Person bereits bezahlt hat. Für den Rest zu dem Gesamtbetrag kopierst Du einfach die innere Select-Anweisung und ziehst das Ergebnis von Deinem erforderlichen Gesamtbetrag ab. Dazu muss aber beachtet werden, dass bisher Leute ohne Bezahlung beim Rest kein Ergebnis stehen haben. Dies schließt Du mit IFNULL aus:

Code: Alles auswählen

SELECT "a"."Betrag", "Kontakte"."Name", (SELECT SUM ("Betrag") FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID) AS "Summe", 250 - IFNULL((SELECT SUM ("Betrag") FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID),0) AS "Rest" FROM "Konto" AS "a", "Kontakte" WHERE "Konto"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 ORDER BY "Kontakte"."Name" ASC

Ich habe hier einfach einmal 250 € als Preis eingesetzt. Vielleicht hast Du ja den eigentlichen Preis auch irgendwo in der Datenbank stehen, damit Du die Datenbank auch im Folgejahr weiter nutzen kannst.

Gruß

Robert

[Der_Paul]

Hallo Robert,

vielen Dank für deine Antwort! Ich denke, ich bin jetzt auf dem richtigen Weg. Aber SQL beschert mir leider momentan noch eine SQL Fehlermeldung. Meine Abfrage habe ich nach deiner Mustervorlage gebaut und sie schaut jetzt so aus:

Code: Alles auswählen

SELECT "a"."Betrag", "Kontakte"."Name", "Konto"."Buchungstag", "Konto"."Verwendungszweck", "Konto"."Termine_ID", (SELECT SUM "Konto"."Betrag" FROM "Konto" WHERE "Konto"."Termine_ID" = 7 AND "Konto"."Kontakt1_ID" = "a"."Kontakt1_ID") AS "Summe", "Termine"."Kosten" - IFNULL((SELECT SUM "Konto"."Betrag" FROM "Konto" WHERE "Konto"."Termine_ID" = 7 AND "Konto"."Kontakt1_ID" = "a"."Kontakt1_ID"),0) AS "Rest" FROM "Konto" AS "a", "Kontakte" WHERE "Kotno"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 ORDER BY "Kontakte"."Name" ASC

Hilft die Datei im Anhang mit den Tabellenzuweisungen weiter? Oder hier auch der Link zur Datei in der Dropbox.
https://www.dropbox.com/s/ltmwrf8kz10cc ... g.jpg?dl=0

Viele Grüße
Paul

[RobertG]

Hallo Paul,

wenn Du eine Fehlermeldung erhältst - kannst Du die hier posten, sofern sie nicht völlig nichtssagend ist?

Wenn Du Deinen Code aus Base heraus kopiert und hier eingefügt hast: Da steht zum Schluss einmal "Kotno" statt "Konto"
Außerdem steht da eine Tabelle "Termine" drin, die nicht in den Beziehungen aufgeführt wird. Da müsste noch

Code: Alles auswählen

... FROM "Konto" AS "a", "Kontakte", "Termine" WHERE ... AND "Termine"."ID" = "Konto"."Termine_ID" ...

eingefügt werden.

Gruß

Robert

[Der_Paul]

Hallo Robert,

die Fehlermeldung sagt leider nicht so besonders viel aus. Zumindest nicht für mich.

SQL-Status: HY000
Fehler-Code: 1000

Syntaxfehler im SQL-Ausdruck
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Und hier meine überarbeitete Abfrage

Code: Alles auswählen

SELECT "a"."Betrag", "Kontakte"."Name", "Konto"."Buchungstag", "Konto"."Verwendungszweck", "Konto"."Termine_ID", (SELECT SUM "Konto"."Betrag" FROM "Konto" WHERE "Konto"."Termine_ID" = 7 AND "Konto"."Kontakt1_ID" = "a"."Kontakt1_ID") AS "Summe", "Termine"."Kosten" - IFNULL((SELECT SUM "Konto"."Betrag" FROM "Konto" WHERE "Konto"."Termine_ID" = 7 AND "Konto"."Kontakt1_ID" = "a"."Kontakt1_ID"),0) AS "Rest" FROM "Konto" AS "a", "Kontakte", "Termine" WHERE "Konto"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 ORDER BY "Kontakte"."Name" ASC

Grüße
Paul

[RobertG]

Hallo Paul,

zum einen war ein Fehler in meinem Code: nach SUM muss das entsprechenden Feld in Klammern gesetzt werden. Habe ich bei meinem Code nachträglich gemacht.
In Deinem Code fehlt außerdem noch die Beziehungsdefinition für die Tabelle "Termine".
Der Code müsste also vermutlich so laufen:

Code: Alles auswählen

SELECT "a"."Betrag", "Kontakte"."Name", "a"."Buchungstag", "a"."Verwendungszweck", "a"."Termine_ID", 
(SELECT SUM ("Betrag") FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID") AS "Summe", 
"Termine"."Kosten" - IFNULL((SELECT SUM ("Betrag") FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID"),0) AS "Rest" 
FROM "Konto" AS "a", "Kontakte", "Termine" 
WHERE "Konto"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 AND "Termine"."ID" = "Konto"."Termine_ID" 
ORDER BY "Kontakte"."Name" ASC

Außerdem hast Du in den Code immer wieder die Tabelle "Konto" eingebaut, obwohl der Tabelle ja ein Alias "a" zugewiesen wurde.

Versuche einmal den entsprechenden Code.

Gruß

Robert

[Der_Paul]

Hallo Robert,

es klappt noch nicht ganz. Bei der Abfrage von dir wirft er mir ein fröhliches:

SQL-Status: S0022
Fehler-Code: -28

Column not found: Konto.Kontakt1_ID in statement [SELECT "a"."Betrag", "Kontakte"."Name", "a"."Buchungstag", "a"."Verwendungszweck", "a"."Termine_ID", ( SELECT SUM( "Betrag" ) FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID" ) "Summe", "Termine"."Kosten" - IFNULL( ( SELECT SUM( "Betrag" ) FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID" ), 0 ) "Rest" FROM "Konto" AS "a", "Kontakte", "Termine" WHERE "Konto"."Kontakt1_ID" = "Kontakte"."ID" AND "Konto"."Termine_ID" = 7 AND "Termine"."ID" = "Konto"."Termine_ID" ORDER BY "Kontakte"."Name" ASC]

entgegen.

Wenn ich "Konto"."Kontakte1_ID" ändere in "a"."Kontakte1_ID" kommt die gleiche Fehlermeldung wie oben nur mit "Konto"."Termine_ID". Also ändere ich "Konto"."Termine_ID" zweimal in "Konto"."Termine_ID".

Der Code der Abfrage sieht dann jetzt so aus:

Code: Alles auswählen

SELECT "a"."Betrag", "Kontakte"."Name", "a"."Buchungstag", "a"."Verwendungszweck", "a"."Termine_ID", 
(SELECT SUM ("Betrag") FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID") AS "Summe", 
"Termine"."Kosten" - IFNULL((SELECT SUM ("Betrag") FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID"),0) AS "Rest" 
FROM "Konto" AS "a", "Kontakte", "Termine" 
WHERE "a"."Kontakt1_ID" = "Kontakte"."ID" AND "a"."Termine_ID" = 7 AND "Termine"."ID" = "a"."Termine_ID" 
ORDER BY "Kontakte"."Name" ASC

Und SQL meldet:

Die Dateninhalte konnten nicht geladen werden.

Set Function on non-numeric data is not allowed in statement [SELECT "a"."Betrag", "Kontakte"."Name", "a"."Buchungstag", "a"."Verwendungszweck", "a"."Termine_ID", ( SELECT SUM( "Betrag" ) FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID" ) "Summe", "Termine"."Kosten" - IFNULL( ( SELECT SUM( "Betrag" ) FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID" ), 0 ) "Rest" FROM "Konto" AS "a", "Kontakte", "Termine" WHERE "a"."Kontakt1_ID" = "Kontakte"."ID" AND "a"."Termine_ID" = 7 AND "Termine"."ID" = "a"."Termine_ID" ORDER BY "Kontakte"."Name" ASC]

Ah! Ich Held der Arbeit. Beim Import der Daten aus dem CSV der Bank habe ich das Feld "Betrag" als VAR (Char) angelegt. Warum auch immer. So kann das ja auch nicht funktionieren.

Feld in Integer gewandelt und schon läuft die Abfrage von dir Robert.

[RobertG]

Hallo Paul,

ein Währungsfeld solltest Du nicht als Integer definieren - es sei denn, Du arbeitest grundsätzlich nur mit ganzen Eurobeträgen. Sonst als Dezimal mit zwei Nachkommastellen (oder auch Numeric, verhält sich gleich).

Ja, der Alias muss natürlich auch in den Beziehungsdefinitionen wieder erscheinen. Solche Fehler schleichen sich dann ein, wenn eben schnell etwas zusammengestückelt wird, ohne dass die DB zum Ausprobieren da ist.

Gruß

Robert

[Der_Paul]

Hallo Robert,

vielen Dank für den Währungshinweis. An die Nachkommastellen hatte ich in dem Moment gar nicht gedacht. Ich war nur froh, dass die Abfrage das machte was sie sollte. Die Datenbank stelle ich gerne zur Verfügung. Macht für die Fehleranalyse auch wirklich mehr Sinn. Hast du einen Tipp für mich, wie ich am einfachsten Tabellen leeren kann?

Ich versuche jetzt "nur" noch die Abfrage dahingehend zu ändern, dass wenn Kontakt2_ID nicht leer ist "Termine"."Kosten" verdoppelt wird.

Code: Alles auswählen

SELECT "a"."Betrag", "Kontakte"."Name", "Kontakte"."Vorname", "Kontakte_1"."Vorname", "a"."Buchungstag", "a"."Verwendungszweck", "a"."Termine_ID",
( SELECT SUM( "Betrag" ) FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID" ) "Summe",
CASE
	WHEN "a"."Kontakt2_ID" IS NOT NULL THEN
	"Termine"."Kosten" * 2 - IFNULL( ( SELECT SUM( "Betrag" ) FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID" ), 0 ) "Rest",
	ELSE
	"Termine"."Kosten" - IFNULL( ( SELECT SUM( "Betrag" ) FROM "Konto" WHERE "Termine_ID" = 7 AND "Kontakt1_ID" = "a"."Kontakt1_ID" ), 0 ) "Rest",
END CASE
FROM { oj "Konto" "a" LEFT OUTER JOIN "Kontakte" "Kontakte_1" ON "a"."Kontakt2_ID" = "Kontakte_1"."ID" }, "Kontakte", "Termine" WHERE "a"."Kontakt1_ID" = "Kontakte"."ID" AND "a"."Termine_ID" = 7 AND "Termine"."ID" = "a"."Termine_ID" ORDER BY "Kontakte"."Name" ASC

Er gibt mir da die folgende Fehlermeldung aus. Irgendwo habe ich bestimmt die Syntax nicht eingehalten.

SQL-Status: HY000
Fehler-Code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIK

[RobertG]

Hallo Paul,

zuerst fällt mir die CASE WHEN-Funktion auf:

Code: Alles auswählen

CASE v1 WHEN v2 THEN v3 [ELSE v4] END

oder

Code: Alles auswählen

CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END

Die zweite Variante wirst Du benötigen - ohne CASE am Ende.
Dann zum ersten Ausdruck:

Code: Alles auswählen

NOT "a"."Kontakt2_ID" IS NULL

IS NULL gehört zusammen, NOT ist die Verneinung der Bedingung zusammen mit dem Feld.

Grundsätzlich gehe ich bei der Zusammenstellung von Abfragen immer so vor: Neue Elemente separat in einer neuen Abfrage testen und anschließend einbauen. Bei Misserfolgen so lange die Abfrage reduzieren, bis mir SQL den Fehler in vernünftiger Form aufzeigt.

Die Aliaszuweisung sollte dann auch zum Schluss nach der Bedingung stehen.

Tip zum Löschen des Inhaltes von Tabellen:

Code: Alles auswählen

DELETE FROM "Tabellenname"

löscht den Inhalt einer Tabelle.

Gruß

Robert