Programmieren - alles kontrollieren 4.941 Themen, 20.708 Beiträge

Verfolgung starten Optionen

Saldo in einer SQL Abfrage

Anwender / 21 Antworten / Baumansicht Nickles

Hi, ich suche wiedermal Hilfe zu einer SQL Abfrage.
Ich habe eine Tabelle mit einer Spalte "Einnahmen" und einer Spalte "Ausgaben". Nun möchte ich eine dritte Spalte mit dem errechneten Saldo je Zeile. Ist das möglich?

mfg Anwender

bei Antwort benachrichtigen
d-oli Anwender „Saldo in einer SQL Abfrage“
Optionen

Ja.



ALTER TABLE <table_name>

   ADD saldo  <column_type>;

Damit fügst Du eine Splate hinzu.

Den errechneten Saldo kannst Du per INSERT einfügen oder per AFTER INSERT Trigger berechenen und einfügen lassen.

Gruss
d-oli
Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen
Anwender d-oli „Ja. ALTER TABLE ADD saldo Damit fügst Du eine Splate hinzu. Den errechneten...“
Optionen

Ich versteh nicht ganz. Könntest du mir ein Bespiel machen?

Tabellenname: tblSaldo
Spalte Einnahmnen: ein
Spalte Ausgaben: aus

Wäre für mich verständlicher. Danke

Die errechnete Spalte muss nicht in der DB gespeichert werden. Sie dient nur der Anzeige.

bei Antwort benachrichtigen
d-oli Anwender „Ich versteh nicht ganz. Könntest du mir ein Bespiel machen? Tabellenname:...“
Optionen

Okay ... (?)

Vermutlioch hast Du die Tabelle so ähnlich erzeugt:



CREATE TABLE tblSaldo(

   ein        float,

   aus       float );

Um jetzt die Spalte "saldo" hinzu zu fügen, führst Du folgendes SQL Script aus: 


ALTER TABLE tblSaldo

   ADD saldo float;

Jetzt sollte die Tabelle diese Signatur haben:
|-----|-----|-------|
| Ein | Aus | Saldo |
|-----|-----|-------|
| 0.0 | 0.0 | 0.0   |
|-----|-----|-------|
Nun weiss ich nicht, ob Du den Saldo automatisch berechnen willst oder ob irgend ein Program den berechneten Wert liefert und Du ihn nur noch einfügen musst.
Kommt der Saldo bereits berechnet daher, sollte das INSERT Statement so aussehen: 


INSERT INTO tblSaldo( ein, aus, saldo ) VALUES( 1023.05, 23.05, 1000 );

Willst Du den Saldo automatisch berechnen kannst Du das z.B. mit einem AFTER INSERT Trigger erledigen. Datenbanksysteme wir Oracle, MySQL,PostgreSQL, MS Sql und ev. sogar Access können so was. Das INSERT Statement würde dann so aussehen: 


INSERT INTO tblSaldo( ein, aus ) VALUES( 1023.05, 23.05 );

Der Saldo wird, nach dem der Datensatz eingefügt würde vom AFTER INSERT Trigger berechnet und dem Datensatz per UPDATE Statement hinzugefügt.
Wie so ein Trigger erzeugt wird findest Du hier:
http://www.techonthenet.com/oracle/triggers/index.php
Hier findest DU allgemeines über SQL:
http://www.w3schools.com/sql/default.asp

d-oli
Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen
Anwender d-oli „Okay ... ? Vermutlioch hast Du die Tabelle so ähnlich erzeugt: CREATE TABLE...“
Optionen

Danke für deine Ausführungen. Ich habe mir das anders vorgestellt:

Ich habe eine AccessDB mit einer Tabelle (tblSaldo) die 2 Spalten (Ein, Aus) enthält. Ich mache in VB nun eine Abfrage auf diese Tabelle und zeige das Resultat in einen DataGrid an. In der Abfrage möchte ich automatisch den Saldo auf jeder Zeile berechnen. Das sieht so aus: Einnahmen - Ausgaben = Saldo. Beim nächsten Datensatz dann: Den Saldo des vorherigen Datensatzes + Einnahmen - Ausgaben = neuer Saldo. Und das die ganze Tabelle durch.
Diese errechnete Spalte wird dann als dritte Spalte im DataGrid angezeigt aber nicht in die Datenbank geschrieben.

Ist das so überhaupt möglich in einer Abfrage zu lösen?

Anwender

bei Antwort benachrichtigen
d-oli Anwender „Danke für deine Ausführungen. Ich habe mir das anders vorgestellt: Ich habe...“
Optionen

Ja, ich denke schon. Ich habe sowas schon mit VBA gemacht:

   Me.ListeSaldo.RowSource = "SELECT ein, aus FROM tSaldo"
   Me.ListeSaldo.Requery
   ... jetzt berechnen etc.

So oder so ähnlich hat das funktioniert.

Mit zusätzlicher Spalte und UPDATE würde es so funktionieren.

Ich habe das schnell mit Oracle und SqlPlus ausprobiert, beachte das UPDATE Statement:

SQL> create table t_saldo( ein float, aus float, saldo float );

Table created.

SQL> desc t_saldo;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EIN                                                FLOAT(126)
 AUS                                                FLOAT(126)
 SALDO                                              FLOAT(126)

SQL> insert into t_saldo( ein, aus ) values( 1023.05, 23.05 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_saldo;

       EIN        AUS      SALDO
---------- ---------- ----------
   1023.05      23.05

SQL> update t_saldo set saldo = ein - aus;

1 row updated.

SQL> commit;

Commit complete.

  
SQL> select * from t_saldo;

       EIN        AUS      SALDO
---------- ---------- ----------
   1023.05      23.05       1000

Das müsste eigentlich auch mit Access Jet-SQL funktionieren.

d-oli

Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen
Anwender d-oli „Ja, ich denke schon. Ich habe sowas schon mit VBA gemacht:...“
Optionen

Für den Saldo des ersten Datensatzes zu berechnen ist das ok.
Aber für alle weitere Datensätze den Saldo zu berechnen, verstehe ich immer noch nicht.

Datensatz ----- Ein------- Aus -------Saldo

1 --------- 1023.05 ---- 23.05 ---- 1000.00
2 ------------54.00 ----------------????.??
3 ---------------------- 12.35 ---- ????.??
4 --------------------- 231.90 ---- ????.??

(Schlechte Formatierung :)


Es muss ja immer der Saldo des vorherigen Datensatzes berücksichtigt werden um den aktuellen Datensatz zu berechnen.
Wie würdest du das machen?

Anwender

bei Antwort benachrichtigen
d-oli Anwender „Für den Saldo des ersten Datensatzes zu berechnen ist das ok. Aber für alle...“
Optionen

Hmmm ... könnte es sein, dass die Tabelle immer nur eine Zeile haben soll?
In diesem Fall würde das so funktionieren:

   -- Datensatz1 Ein 1023.05 Aus 23.05 Saldo 1000.00
   insert into t_saldo( ein, aus ) values( 1023.05, 23.05 ); commit;

   -- Datensatz2 Ein 54.00 Saldo ????.??
   update t_saldo set ein = ein + 54.00; commit;
   update t_saldo set saldo = ein - aus; commit;

   -- Datensatz3 Aus 12.35 Saldo ????.??
   update t_saldo set aus = aus + 12.35; commit;
   update t_saldo set saldo = ein - aus; commit;

   -- Datensatz4 Aus 231.90 Saldo ????.??
   update t_saldo set aus = aus + 231.90; commit;
   update t_saldo set saldo = ein - aus; commit;

Andererseits könntest Du auch mit der Funktion SUM() arbeiten um damit den Datengrid zu füllen (siehe auch vorangehende angepasste Antwort betreffend VBA):

SQL>
   -- Datensatz1 Ein 1023.05 Aus 23.05 Saldo 1000.00
   insert into t_saldo( ein, aus ) values( 1023.05, 23.05 ); commit;

   -- Datensatz2 Ein 54.00 Saldo ????.??
   insert into t_saldo( ein, aus ) values( 54.00, 0 ); commit;

   -- Datensatz3 Aus 12.35 Saldo ????.??
   insert into t_saldo( ein, aus ) values( 0, 12.35 ); commit;

   -- Datensatz4 Aus 231.90 Saldo ????.??
   insert into t_saldo( ein, aus ) values( 0, 231.90 ); commit;

VBA>
   Me.ListeSaldo.RowSource = "SELECT SUM( ein ), SUM( aus ) FROM tSaldo"
   Me.ListeSaldo.Requery


d-oli

Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen
d-oli Nachtrag zu: „Hmmm ... könnte es sein, dass die Tabelle immer nur eine Zeile haben soll? In...“
Optionen

Ich muss jetzt leider gehen, sollte noch was sein, werde ich mir das morgen ansehen ... ;-)
Gruss
d-oli

Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen
Anwender Nachtrag zu: „Danke für deine Ausführungen. Ich habe mir das anders vorgestellt: Ich habe...“
Optionen

Am liebsten würde ich das nur mit einem SELECT machen und ohne UPDATE, INSERT und ALTER TABLE.
Ich will den Saldo nicht in der Tabelle führen, nur zur Laufzeit errechnen und darstellen

bei Antwort benachrichtigen
fgh443 Anwender „Am liebsten würde ich das nur mit einem SELECT machen und ohne UPDATE, INSERT...“
Optionen
SELECT Tab.einnahme, Tab.ausgabe, ([einnahme]-[ausgabe]) AS Summe
FROM Tab;


So?
Mist, schon wieder was gelernt...
bei Antwort benachrichtigen
Anwender fgh443 „SELECT Tab.einnahme, Tab.ausgabe, einnahme - ausgabe AS Summe FROM Tab So? “
Optionen

Nein! So wird nur die Summe pro Datensatz errechnet. Ich brauche aber die laufende Summe (Aufgerechnet der Summen verherigen Datensätze).

bei Antwort benachrichtigen
d-oli Anwender „Am liebsten würde ich das nur mit einem SELECT machen und ohne UPDATE, INSERT...“
Optionen

Okay, dann so wie schon beschrieben:

SQL>
-- Datensatz1 Ein 1023.05 Aus 23.05 Saldo 1000.00
insert into t_saldo( ein, aus ) values( 1023.05, 23.05 ); commit;

-- Datensatz2 Ein 54.00 Saldo ????.??
insert into t_saldo( ein, aus ) values( 54.00, 0 ); commit;

-- Datensatz3 Aus 12.35 Saldo ????.??
insert into t_saldo( ein, aus ) values( 0, 12.35 ); commit;

-- Datensatz4 Aus 231.90 Saldo ????.??
insert into t_saldo( ein, aus ) values( 0, 231.90 ); commit;

VBA>
Me.ListeSaldo.RowSource = "SELECT SUM( ein ), SUM( aus ), 0 FROM tSaldo"
Me.ListeSaldo.Requery


Die "0" in "SELECT SUM( ein ), SUM( aus ), 0 FROM tSaldo" steht für den leeren Saldo, den Du berechnen und im DatenGrid nachführen musst.
So, ich gehe jetzt an die Sonne ... ;-) ... bis ev. morgen.

d-oli

Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen
Borlander Anwender „Danke für deine Ausführungen. Ich habe mir das anders vorgestellt: Ich habe...“
Optionen
Einnahmen - Ausgaben = Saldo. Beim nächsten Datensatz dann: Den Saldo des vorherigen Datensatzes + Einnahmen - Ausgaben = neuer Saldo. Und das die ganze Tabelle durch. [...] Ist das so überhaupt möglich in einer Abfrage zu lösen?
Ja, mit Subselects wäre das möglich - wird aber eine extrem teure Abfrage (zumindest bei größeren Tabellen. Mit 100 Datensätzen mag das noch laufen, mit 10000 macht es aber sicher keinen Spaß mehr das Warten). Zwingend notwendig dafür wäre noch ein Unique Feld aus dem sich dann die Sortierreihenfolge ergeben würde. Am einfachsten wäre da ein AutoInc-Feld, dann hat man auch gleich die Insert-Reihenfolge der Datensätze.

Die Abfrage sähe dann so aus:

SELECT ein, aus, (SELECT SUM(ein)-SUM(aus) FROM table WHERE id FROM table AS t
ORDER BY id ASC

Da zumindest bei den hinteren Datensätzen ein FullTable-Scan im Subselct zu erwarten ist wäre zu prüfen ob bei diesem eine vorsortierung der Tabelle zur optimierung berücksichtig wird. Das kann je nach DB-Server unterschiedlich aussehen.

Zu bevorzugen wäre auf jeden Fall eine berchnung im Client, da man dort ein lineares Laufzeitverhalten erwarten kann. Bei meiner SQL-Abfrage ist ein qudratisches Laufzeitverhalten zu erwarten!


Gruß
Borlander
bei Antwort benachrichtigen
Anwender Borlander „ Ja, mit Subselects wäre das möglich - wird aber eine extrem teure Abfrage...“
Optionen

So ähnlich habe ich mir das vorgestellt. Hab das jetzt noch nicht ausprobiert, mach ich aber noch.
Ich habe im Internet etwas über einen Lösungsansatz mit der Funktion DSUM() (http://support.microsoft.com/?kbid=290136) gelesen. Kann das für meinen Fall aber nicht richtig interpretieren. Wäre das evt eine Lösung?

mfg Anwender

bei Antwort benachrichtigen
Borlander Anwender „So ähnlich habe ich mir das vorgestellt. Hab das jetzt noch nicht ausprobiert,...“
Optionen
DSUM() [...] Wäre das evt eine Lösung?
Schaut für mich auf den ersten Blick so aus. Scheint kein Bestandteil von ANSI-SQL zu sein sondern was MS-Spezifisches...

Was sagt denn die Access-Hilfe (so lange es nicht um VBA geht ist die Hilfe von MSO nämlich recht gut) zum Thema DSum oder Domainfunktionen? Im internet scheint dazu nicht viel zu finden zu sein, außer daß die Funktionen alles andere als schnell sein sollen...


Gruß
Borlander
bei Antwort benachrichtigen
Anwender Borlander „ Schaut für mich auf den ersten Blick so aus. Scheint kein Bestandteil von...“
Optionen

Die Access Hilfe hilft in diesem Fall nicht weiter.

Habe deine Abfrage von oben implementiert und sie funktioniert so wie ich mir das vorstelle. Vielen Dank!
Einziger Nachteil: bei meinen 4000 Datensätzen dauert die Abfrage nun volle 14 Sekunden! Naja war wohl zu erwarten.

Wenn es aber eine effizientere Möglichkeit gibt, wär ich natürich dankbar für weitere Infos.

mfg Anwender

bei Antwort benachrichtigen
Borlander Anwender „Die Access Hilfe hilft in diesem Fall nicht weiter. Habe deine Abfrage von oben...“
Optionen
Die Access Hilfe hilft in diesem Fall nicht weiter.
Haben die denn kein Beispiel dazu in der Hilfe?
Ansonsten einfach mal ausprobieren was ein
SELECT *, DSUM(ein-aus) FROM table
macht...

Einziger Nachteil: bei meinen 4000 Datensätzen dauert die Abfrage nun volle 14 Sekunden! Naja war wohl zu erwarten.
Ja, das hatte ich ja oben schon umfangreich befürchtet. Wenn der Datenbestand noch größer wird ist schnell der Punkt erreicht an dem das nicht mehr praktikabel ist. Bei 8000 Datensätzen wären wir dann schon bei rund einer Minute Abfragedauer (Schätzung nach obiger Annahme von n²-Laufzeitverhalten )...

Wenn es aber eine effizientere Möglichkeit gibt
Im Client aufsummieren wäre wie schon genannt die eleganteste Lösung

Wenn sich an den Daten nur selten was ändert könnte man es natürlich cachen, wäre mit einem View mit eigener Ergebnistabelle sogar transparent möglich. Wenn bestehende Datensätze sich nicht mehr verändern werden könnte man das ganze auch wie schon oben vorgeschlagen mittelt Trigger-Funktion im Datensatz speichern lassen...


Gruß
Borlander
bei Antwort benachrichtigen
Borlander d-oli „Okay ... ? Vermutlioch hast Du die Tabelle so ähnlich erzeugt: CREATE TABLE...“
Optionen

Nur so als Anmerkung: Für Geldbeträge sollte man tunlichst keine float-Felder einsetzen sondern Grundsätzlich Ganz oder Festkommazahlen. Rechenungenauigkeiten sind bei solchen Anwendungen eine sehr böse Sache...

Gruß
Borlander

bei Antwort benachrichtigen
d-oli Borlander „Nur so als Anmerkung: Für Geldbeträge sollte man tunlichst keine float-Felder...“
Optionen

Ja, klar. Ich habe das weggelassen damit nicht noch ein Diskusion ob jetzt numeric(p,s) oder decimal(p,s) und warum nicht doch real, double oder float und wie viel Stellen vor dem Komma denn nun und gibt es denn nicht auch RDBMS die den Datentypen CURRENCY unterstützen ... und und und ... uff, kennst Du das auch?

Meiner Ansicht ist der grösste Schwachpunkt sowieso, dass kein Primärschlüssel vorhanden ist. Bei einer (doppelten) Buchhaltung müssten alle Buchungen mit einer Buchungsnummer versehen werden die eindeutig sein sollte (=Primärschlüssel). Auch ein Datum gehört meiner Ansicht nach zu einer Buchung. Aber das ginge hier wohl zu weit.

Gruss
d-oli

Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen
Anwender d-oli „Ja, klar. Ich habe das weggelassen damit nicht noch ein Diskusion ob jetzt...“
Optionen

ID (Primärschlüssel), Datum, Valuta, Buchungstext, ... alles vorhanden. Wollte nur das Beispiel einfach halten.

mfg Anwender

bei Antwort benachrichtigen
d-oli Anwender „ID Primärschlüssel , Datum, Valuta, Buchungstext, ... alles vorhanden. Wollte...“
Optionen

Na dann ... ;-)
Für komplexere Sachen ist es manchmal besser mehr zu erfahren.
Ich würde übrigens die Lösung mit einer zusätzlichen Spalte "Aktueller Saldo" und dem Nachführen des aktuellen Saldos per Trigger bevorzugen.

Gruss
d-oli

Konstruktive Kritik zeichnet sich dadurch aus, dass sie höflich, nützlich und sachlich ist.
bei Antwort benachrichtigen