SQLITE SQL mit coalesce{gelöst]

[Johannes55]

Hallo Forum.
Ich habe eine Verständnisfrage zu coalesce.
Um mein Problem zu verdeutlichen, habe ich meine Abfragen und die Ergebnisse im folgenden dargestellt.
In der SQLITE Datenbank habe ich die Tabellen Konten und Buchungen.
Konten enthält bei Sätzen mit KZ_Saldovortrag einen Anfangsbestand
Tabelle Buchungen enthält alle Buchungen zu diesen Konten.
Es können Konten ohne Buchungen vorhanden sein. Deshalb coalesce.

1. Select: alles korrekt
Select 'G' Gruppe,
'Umlaufvermögen' as KST_Bezeichnung,
ko.konto ,
ko.Anfangsbestand AS Anfangsbestand ,
coalesce(sum(Bu.Einnahmen),0 ) AS Einnahmen ,
coalesce(sum(Bu.Ausgaben),0 ) AS Ausgaben ,
Ko.Anfangsbestand + coalesce(sum(Bu.Einnahmen),0 ) - coalesce(sum(Bu.Ausgaben),0 ) AS Saldo
from Konten Ko
left join Buchungen Bu on Bu.Konto_id = Ko.id
where Ko.Geschaeftsjahr = 2026
and Ko.KZ_Saldovortrag = 'J'
group by Ko.Konto,Gruppe

Konto Anfangsb Einnahmen Ausgaben Saldo
G Umlaufvermögen 1800 0 683.84 12481.84 9836.53 3329.15
G Umlaufvermögen 1830 0 5000.00 0 0 5000.00
G Umlaufvermögen 1840 0 9140.83 1019.48 0 10160.31
14824,67 13501,32 9836,53 18489,46 <<-- manuelle ermittelt

2. Select: Hier soll die Gesamtsumme aller Konten ermittelt werden.
Die Anweisung Sum(Ko.Anfangsbestand) wird jedoch mit jedem gefundenen Satz in Buchungen kumuliert.
Wie muss dazu die korrekte Syntax aussehen?

Select 'G' Gruppe,
'Umlaufvermögen' as KST_Bezeichnung,
sum(ko.Anfangsbestand) AS Anfangsbestand ,
coalesce(sum(Bu.Einnahmen),0 ) AS Einnahmen ,
coalesce(sum(Bu.Ausgaben),0 ) AS Ausgaben ,
sum(Ko.Anfangsbestand) + coalesce(sum(Bu.Einnahmen),0 ) - coalesce(sum(Bu.Ausgaben),0 ) AS Saldo
from Konten Ko
left join Buchungen Bu on Bu.Konto_id = Ko.id
where Ko.Geschaeftsjahr = 2026
and Ko.KZ_Saldovortrag = 'J'
group by Gruppe
Anfangsb Einnahmen Ausgaben Saldo
G Umlaufvermögen 105593.37 13501.32 9836.53 109258.16 <<-- Anfangsbestand 105593.37 muss identische sein mit 1,Beispiel.

Ich hoffe, Ihr könnt die Ergebnisspalten richtig lesen. Der formatierte Entwurf geht leider in der endgültigen Form verloren.
Vielen Dank für Eure Hilfe
Johannes

[laz_mikel]

Der Grund liegt in "group by Ko.Konto,Gruppe" versus "group by Gruppe".
Die zweite Gruppierung fast die gesamte Gruppe zusammen, während die erste nur Datensätze zum gleichen Konto zusammenfasst - und dann erst die Gruppe.

[Johannes55]

Hallo.
Beim zweiten Select will ich doch eine Gesamtsumme aller Konten.
Deshalb Group by Gruppe.

[laz_mikel]

Dann habe ich das mit dem "...wird jedoch mit jedem gefundenen Satz in Buchungen kumuliert." falsch verstanden, den das klang für mich so, dass das nicht erwünscht sei.

Ich vermute mal - ohne es selber ausprobiert zu haben - dass die Verwendung des Code-Schalters ("</>") und ein paar passende Leerzeichen die Abfragen leserlicher gemacht hätte.

Mir kommt es auch so vor, dass die erste Abfrage von der Spaltenanzahl im Ergebnis (8 Spalten) nicht zur Abfrage (7 Spalten) passt.
Aber vielleicht habe ich da auch etwas übersehen.

Ob die HAVING-Klausel hier nicht gegenüber der WHERE-Klausel besser gewesen wäre, würde ich auch prüfen.
Aber das können hier vielleicht andere besser beurteilen.

[Johannes55]

Da beides eigenständige Select Anweisungen sind, stimmt die Spaltenanzahl.
In meiner Anwendung werden die mit Union Select und zusätzlicher Spalte zusammengefasst und da passen die Spalten.

Zur Lesbarkeit hatte ich geschrieben, da ich </> nicht kannte:
@ch hoffe, Ihr könnt die Ergebnisspalten richtig lesen. Der formatierte Entwurf geht leider in der endgültigen Form verloren.

[paweld]

Genau hier zeigt sich der besondere Reiz von SQLite – es sollte (anders als beispielsweise bei MSSQL) nicht zulassen, dass in einer Abfrage eine nicht aggregierte Spalte verwendet wird, die nicht im Gruppierungsabschnitt aufgeführt ist – dann wäre es sehr einfach, den Fehler aufzuspüren.
Um die Abfrage zu korrigieren, muss man eine Unterabfrage verwenden, d. h. die Umsatz-Tabelle muss zuvor aggregiert werden, bevor sie in der Hauptabfrage verwendet wird.
Gesamtsumme:

Code: Alles auswählen

Select 'G' Gruppe,
'Umlaufvermögen' KST_Bezeichnung,
sum(ko.Anfangsbestand) Anfangsbestand ,
coalesce(sum(Bu.Einnahmen),0 ) Einnahmen ,
coalesce(sum(Bu.Ausgaben),0 ) Ausgaben ,
sum(Ko.Anfangsbestand) + coalesce(sum(Bu.Einnahmen),0 ) - coalesce(sum(Bu.Ausgaben),0 ) Saldo
from Konten Ko
left join (select Konto_id, sum(Einnahmen) Einnahmen, sum(Ausgaben) Ausgaben from Buchungen group by Konto_id) Bu on (Bu.Konto_id = Ko.id)
where Ko.Geschaeftsjahr = 2026
and Ko.KZ_Saldovortrag = 'J'

Aufgeschlüsselt nach Konten:

Code: Alles auswählen

Select 'G' Gruppe,
'Umlaufvermögen' KST_Bezeichnung,
ko.konto, 
sum(ko.Anfangsbestand) Anfangsbestand ,
coalesce(sum(Bu.Einnahmen),0 ) Einnahmen ,
coalesce(sum(Bu.Ausgaben),0 ) Ausgaben ,
sum(Ko.Anfangsbestand) + coalesce(sum(Bu.Einnahmen),0 ) - coalesce(sum(Bu.Ausgaben),0 ) Saldo
from Konten Ko
left join (select Konto_id, sum(Einnahmen) Einnahmen, sum(Ausgaben) Ausgaben from Buchungen group by Konto_id) Bu on (Bu.Konto_id = Ko.id)
where Ko.Geschaeftsjahr = 2026
and Ko.KZ_Saldovortrag = 'J'
group by ko.konto

[Zvoni]

Ähhh.....pawel....dein SQL ergibt für mich keinen Sinn (auch wenn es das richtige Ergebnis liefert)

Ich baue das ganze mal auf CTE um, weil es dann leichter zu lesen ist

Gesamtsumme

Code: Alles auswählen

WITH
  BU AS (select sum(Einnahmen) Einnahmen, sum(Ausgaben) Ausgaben 
              from Buchungen)  -- --> Konto_id entfernt. Wir wollen die Summe unabhängig von Konto_id
  
Select 'G' Gruppe,
'Umlaufvermögen' KST_Bezeichnung,
sum(ko.Anfangsbestand) Anfangsbestand ,
coalesce(Bu.Einnahmen,0 ) Einnahmen ,  -- SUM entfernt, da wir sie vom Sub-Select bereits bekommen
coalesce(Bu.Ausgaben,0 ) Ausgaben , -- SUM entfernt, da wir sie vom Sub-Select bereits bekommen
sum(Ko.Anfangsbestand) + coalesce(Bu.Einnahmen,0 ) - coalesce(Bu.Ausgaben,0 ) Saldo  -- SUM entfernt, da wir sie vom Sub-Select bereits bekommen
from Konten Ko
left join Bu on 1=1  -- Wir haben kein Verknüpfungsfeld, und der Sub-Select liefert genau eine Zeile
where Ko.Geschaeftsjahr = 2026
and Ko.KZ_Saldovortrag = 'J'

Nach Konto

Code: Alles auswählen

WITH
  BU AS (select Konto_id, sum(Einnahmen) Einnahmen, sum(Ausgaben) Ausgaben 
              from Buchungen group by Konto_id) 
Select 'G' Gruppe,
'Umlaufvermögen' KST_Bezeichnung,
ko.konto, 
ko.Anfangsbestand Anfangsbestand , -- SUM entfernt, da wir pro Konto ermitteln, und da hat es nur einen Wert
coalesce(Bu.Einnahmen,0 ) Einnahmen ,  -- SUM entfernt, da wir sie vom Sub-Select bereits bekommen
coalesce(Bu.Ausgaben,0 ) Ausgaben , -- SUM entfernt, da wir sie vom Sub-Select bereits bekommen

Ko.Anfangsbestand +   -- SUM entfernt, da wir pro Konto ermitteln, und da hat es nur einen Wert
  coalesce(Bu.Einnahmen,0 ) - coalesce(Bu.Ausgaben,0 ) Saldo  -- SUM entfernt, da wir sie vom Sub-Select bereits bekommen

from Konten Ko
left join Bu on Bu.Konto_id = Ko.id
where Ko.Geschaeftsjahr = 2026
and Ko.KZ_Saldovortrag = 'J'
-- group by ko.konto  --> Kein Group By Notwendig!!

Generell: Ich stimme aber Pawel zu: Es ist furchtbar, dass SQLite Gruppierung/Aggregierung ohne Group By zulässt bzw. dass man in der Gruppierung Spalten weglassen kann.
Was bin ich da schon auf die Schnauze gefallen.

Prinzipiell bei solchen Szenarien: Erst Summe bilden, dann Joinen

[paweld]

Ähhh.....pawel....dein SQL ergibt für mich keinen Sinn (auch wenn es das richtige Ergebnis liefert)

Ich baue das ganze mal auf CTE um, weil es dann leichter zu lesen ist

Jeder hat seine eigenen Vorlieben. Ich persönlich finde, dass Unterabfragen wesentlich übersichtlicher sind. Ich versuche, CTE zu vermeiden, ähnlich wie with in Pascal.

Ich habe „konto_id“ in der allgemeinen Gruppierung nicht weggelassen, da wir auf diese Weise eine einzige Abfrage hätten, die an vielen Stellen verwendet wird, und dies hat praktisch keinen Einfluss auf die Leistung.