[MySQL 5] Unlogische Ergebnisse bei UNION-Abfrage

[Lennynero]

Hallo,

gegeben ist die DB aus dem Anhang (4 Tabellen).

Dazu passend habe ich eine Abfrage erstellt, die mir für die einzelnen Konten Summen über die einzelnen Monate bildet (ich habe mich bei der Abfrage hier nur mal auf 2 Monate und die Jahressumme beschränkt).

Im ersten Teil der Union wird die Abfrage für alle einzelnen Konten durchgeführt, im zweiten Teil dann gruppiert nach Ertrag und Aufwand, der vierte Teil stellt lediglich Überschriften bereit und im vierten Teil soll dann die Summe aus allen gebildet werden

################### Nummer 1 ###################
SELECT
  konten.konto_id, 
  konten.konto_name,
  
  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
          IF(date_format(steuer.buchung_datum, '%m') = '01', steuer.steuer_betrag, 0), 
          IF(date_format(netto.zahlung_datum, '%m') = '01', netto.netto_betrag, 0))) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 1), 0) 
  AS jan,
  
  
    SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
          IF(date_format(steuer.buchung_datum, '%m') = '02', steuer.steuer_betrag, 0), 
          IF(date_format(netto.zahlung_datum, '%m') = '02', netto.netto_betrag, 0))) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 2), 0) 
  AS feb,

  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), steuer.steuer_betrag, netto.netto_betrag)) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk), 0) 
  AS 'sum',
  konten.konto_typ,
  1 AS sortierung
  
FROM tbl_konten as konten

LEFT JOIN 
  tbl_buchung_netto as netto ON konten.konto_id = netto.konto_id_fk
LEFT JOIN 
  tbl_buchung_steuer as steuer ON konten.konto_id = steuer.konto_steuer_typ_id_fk

WHERE
  konto_id IN (
          SELECT konto_id_fk
          FROM tbl_buchung_netto
          WHERE date_format(zahlung_datum, '%Y') = '2009')
OR
  konto_id IN (
          SELECT konto_steuer_typ_id_fk
          FROM tbl_buchung_steuer
          WHERE date_format(buchung_datum, '%Y') = '2009')

GROUP BY 
  konten.konto_id,
  konten.konto_typ


UNION
  
################### Nummer 2 ###################
SELECT
  MAX(konten.konto_id) + 1 AS konto_id,
  IF(konten.konto_typ = 'ertrag', 'Summe Einnahmen', 'Summe Ausgaben') AS konto_name,
  
  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
      IF(date_format(steuer.buchung_datum, '%m') = '01', steuer.steuer_betrag, 0), 
      IF(date_format(netto.zahlung_datum, '%m') = '01', netto.netto_betrag, 0))) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 1), 0) 
  AS jan,

  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
      IF(date_format(steuer.buchung_datum, '%m') = '02', steuer.steuer_betrag, 0), 
      IF(date_format(netto.zahlung_datum, '%m') = '02', netto.netto_betrag, 0))) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 2), 0) 
  AS feb,


  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), steuer.steuer_betrag, netto.netto_betrag)) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk), 0) AS 'sum',
  konten.konto_typ,
  
  1 AS sortierung
  
FROM tbl_konten as konten

LEFT JOIN tbl_buchung_netto as netto ON konten.konto_id = netto.konto_id_fk
LEFT JOIN tbl_buchung_steuer as steuer ON konten.konto_id = steuer.konto_steuer_typ_id_fk

WHERE
  konto_id IN (
    SELECT konto_id_fk
    FROM tbl_buchung_netto
    WHERE date_format(zahlung_datum, '%Y') = '2009')
  OR
  konto_id IN (
    SELECT konto_steuer_typ_id_fk
    FROM tbl_buchung_steuer
    WHERE date_format(buchung_datum, '%Y') = '2009')

GROUP BY  konten.konto_typ

UNION
  
  
################### Nummer 3 ###################
SELECT
  0 AS konto_id,
  IF(konten.konto_typ = 'ertrag', 'Einnahmen', 'Ausgaben') AS konto_name,
  
  '' AS jan,
  '' AS feb,

  '' AS 'sum',
  
  konten.konto_typ,
  1 AS sortierung
  
FROM tbl_konten as konten

LEFT JOIN tbl_buchung_netto as netto ON konten.konto_id = netto.konto_id_fk
LEFT JOIN tbl_buchung_steuer as steuer ON konten.konto_id = steuer.konto_steuer_typ_id_fk

WHERE
  konto_id IN (
    SELECT konto_id_fk
    FROM tbl_buchung_netto
    WHERE date_format(zahlung_datum, '%Y') = '2009')
  OR
  konto_id IN (
    SELECT konto_steuer_typ_id_fk
    FROM tbl_buchung_steuer
    WHERE date_format(buchung_datum, '%Y') = '2009')

GROUP BY  konten.konto_typ
  
  
  
UNION
  
################### Nummer 4 ###################
SELECT
  MAX(konten.konto_id) + 2 AS konto_id,
  'Summe Gesamt' AS konto_name,
  
  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
        IF(date_format(steuer.buchung_datum, '%m') = '01', steuer.zahlung_steuerbetrag, 0), 
        IF(date_format(netto.zahlung_datum, '%m') = '01', netto.zahlung_betrag, 0))) 
  - 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 1), 0) 
  AS jan,


  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
        IF(date_format(steuer.buchung_datum, '%m') = '02', steuer.zahlung_steuerbetrag, 0), 
        IF(date_format(netto.zahlung_datum, '%m') = '02', netto.zahlung_betrag, 0))) 
  - 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 1), 0) 
  AS feb,

  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), steuer.zahlung_steuerbetrag, netto.zahlung_betrag)) 
  - 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk), 0) 
  AS 'sum',
  
  konten.konto_typ,
  2 AS sortierung
  
FROM tbl_konten as konten

LEFT JOIN tbl_buchung_netto as netto ON konten.konto_id = netto.konto_id_fk
LEFT JOIN tbl_buchung_steuer as steuer ON konten.konto_id = steuer.konto_steuer_typ_id_fk

WHERE
  konto_id IN (
    SELECT konto_id_fk
    FROM tbl_buchung_netto
    WHERE date_format(zahlung_datum, '%Y') = '2009')
OR
  konto_id IN (
    SELECT konto_steuer_typ_id_fk
    FROM tbl_buchung_steuer
    WHERE date_format(buchung_datum, '%Y') = '2009')

  
ORDER BY sortierung, konto_typ DESC, konto_id 

Im Grunde ist das mehrmals die gleiche Abfrage, halt jeweils nur anders gruppiert.

Interessant ist das Konto mit der ID 4001: die Werte können auch aus der Tabelle "tbl_buchung_anpassung" kommen, bei der statt des Datums Monat und Jahr als Integer gespeichert werden. Beim "normalen" aufaddieren der einzelnen Konten werden die Werte ordentlich mitgezählt, wenn ich aber nach Konto-Typ oder gesamt gruppiere, dann fallen die Beträge aus dieser Tabelle unter den Tisch.

Ich hatte auch schon versucht diese Tabelle mithilfe eines LEFT JOIN zu integrieren (sowohl direkt, als auch als einfache Abfrage aus den relevanten Spalten), hatte dann aber das Problem, dass im Januar auf einmal 3000 statt 2000 herauskamen.

Hat da jemand eine kreative Idee?

Gruss,
Lenny

[Lennynero]

Datei vergessen :

[ghostgambler]

Tritt das Problem auch auf, wenn du den einen SELECT ohne UNION absetzt? Wenn ja, dann poste mal bitte nur den einen einzigen Query...
Das Monster da liest doch niemand oO

[Lennynero]

Hi,

auch wenn ich die einzelnen Abschnitte ohne "UNION" aufrufe, bekomme ich die gleichen Ergebnisse (wenn ich die kontentypen oder alles gruppiere und Summen bilde, dann werden die Werte aus der erwähnten Tabelle nicht abgefragt.

SELECT
  konten.konto_id, 
  konten.konto_name,
  
  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
          IF(date_format(steuer.buchung_datum, '%m') = '01', steuer.steuer_betrag, 0), 
          IF(date_format(netto.zahlung_datum, '%m') = '01', netto.netto_betrag, 0))) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 1), 0) 
  AS jan,
  
  
    SUM(IF(konten.konto_id IN(3806,3801,1401,1406), 
          IF(date_format(steuer.buchung_datum, '%m') = '02', steuer.steuer_betrag, 0), 
          IF(date_format(netto.zahlung_datum, '%m') = '02', netto.netto_betrag, 0))) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 2), 0) 
  AS feb,

  SUM(IF(konten.konto_id IN(3806,3801,1401,1406), steuer.steuer_betrag, netto.netto_betrag)) 
  + 
  COALESCE((SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk), 0) 
  AS 'sum',
  konten.konto_typ,
  1 AS sortierung
  
FROM tbl_konten as konten

LEFT JOIN 
  tbl_buchung_netto as netto ON konten.konto_id = netto.konto_id_fk
LEFT JOIN 
  tbl_buchung_steuer as steuer ON konten.konto_id = steuer.konto_steuer_typ_id_fk



GROUP BY 
  ######################
  konten.konto_id,
  ######################
  konten.konto_typ 

Bei dem Datenbestand kann man auch die "WHERE" Statements weglassen, da ich die Datensätze sowieso auf die vorhandenen Konten beschränkt habe.

Das einzige was man bei der Abfrage jetzt änder müsste wäre das Auskommentieren der "konten.konto_id" im GROUP Statement... und schon gehen die 1000 pro Monat verloren.

[ghostgambler]

Prinzipiell ist es immer eine gute Idee den Query erstmal auf das wesentliche zu reduzieren.

SELECT
*
, (SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 1)
, (SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 2)
, (SELECT SUM(betrag_neu) FROM tbl_buchung_anpassung  WHERE  konto_id = konto_id_fk AND monat = 3)
FROM tbl_konten as konten

LEFT JOIN 
  tbl_buchung_netto as netto ON konten.konto_id = netto.konto_id_fk
LEFT JOIN 
  tbl_buchung_steuer as steuer ON konten.konto_id = steuer.konto_steuer_typ_id_fk 

wenn du jetzt noch dein gewünschtes GROUP BY konten.konto_typ hinzufügst, siehst du den Fehler doch praktisch sofort. Du hast einen Subselect auf eine konto_id, nach der du nicht gruppierst. Das geht in anderen DBMS gar nicht erst, mysql gibt dir dann hier halt einfach falsche Werte.

Eine mögliche Lösung wäre zuerst nach konto_id zu gruppieren, dabei die Sub-Selects zu nutzen, und dann über ein
SELECT * FROM (SELECT ... GROUP BY konto_id) GROUP BY konto_typ
zu gehen... ob das allerdings dann noch mit deinem UNION funktioniert musst du selbst mal ausprobieren.
Ansonsten denke ich, dass du fit genug bist in MySQL um da alleine zurecht zu kommen

Prinzipiell ist es immer eine schlechte Idee, aus einer Tabelle, bei der man nach einer Spalte gruppiert, eine andere Spalte ohne Anwendung einer Aggregats-Funktion auf diese zu nutzen.

Beispiel:
id, name, vorname
SELECT vorname FROM tabelle GROUP BY name
es gibt 200 Maiers, wessen Vornamen willst du denn jetzt?
Gleiches Problem. MySQL gibt dir dann irgendeinen zurück.

[Lennynero]

Wenn man es reduziert... ist es wirklich banal

Im vierten Teil habe ich beim SUBSELECT die WHERE-Bedingung gegen "WHERE monat = 1 AND jahr = 2009" ausgetauscht (natürlcih analog zu den anderen Monaten), im zweiten Teil habe ich das SUBSELECT mit einem JOIN erweitert (so komme ich im SUBSELECT dann auch an den Kontentyp, den ich zum Vergleich mit "aussen" benötige)... und siehe da: es funktioniert!

Vielen Dank!