SUM Funktion
Moderator: Moderatoren
SUM Funktion
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
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
Re: SUM Funktion
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:
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:
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
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
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
Gruß
Robert
Zuletzt geändert von RobertG am Mo, 29.02.2016 20:40, insgesamt 1-mal geändert.
Re: SUM Funktion
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:
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
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
https://www.dropbox.com/s/ltmwrf8kz10cc ... g.jpg?dl=0
Viele Grüße
Paul
Re: SUM Funktion
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
eingefügt werden.
Gruß
Robert
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" ...
Gruß
Robert
Re: SUM Funktion
Hallo Robert,
die Fehlermeldung sagt leider nicht so besonders viel aus. Zumindest nicht für mich.
Grüße
Paul
die Fehlermeldung sagt leider nicht so besonders viel aus. Zumindest nicht für mich.
Und hier meine überarbeitete AbfrageSQL-Status: HY000
Fehler-Code: 1000
Syntaxfehler im SQL-Ausdruck
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
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
Paul
Re: SUM Funktion
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:
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
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
Versuche einmal den entsprechenden Code.
Gruß
Robert
Re: SUM Funktion
Hallo Robert,
es klappt noch nicht ganz. Bei der Abfrage von dir wirft er mir ein fröhliches:
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:
Und SQL meldet:
Feld in Integer gewandelt und schon läuft die Abfrage von dir Robert.
es klappt noch nicht ganz. Bei der Abfrage von dir wirft er mir ein fröhliches:
entgegen.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]
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
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.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]
Feld in Integer gewandelt und schon läuft die Abfrage von dir Robert.
Re: SUM Funktion
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
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
Re: SUM Funktion
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.
Er gibt mir da die folgende Fehlermeldung aus. Irgendwo habe ich bestimmt die Syntax nicht eingehalten.
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
SQL-Status: HY000
Fehler-Code: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIK
Re: SUM Funktion
Hallo Paul,
zuerst fällt mir die CASE WHEN-Funktion auf:
oder
Die zweite Variante wirst Du benötigen - ohne CASE am Ende.
Dann zum ersten Ausdruck:
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:
löscht den Inhalt einer Tabelle.
Gruß
Robert
zuerst fällt mir die CASE WHEN-Funktion auf:
Code: Alles auswählen
CASE v1 WHEN v2 THEN v3 [ELSE v4] END
Code: Alles auswählen
CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END
Dann zum ersten Ausdruck:
Code: Alles auswählen
NOT "a"."Kontakt2_ID" IS NULL
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"
Gruß
Robert