union und sum problem bei mysql

  • Ich habe ein Problem mit union (über 3 Tabellen), die Ausgabe sollte Spaltensummen ergeben, also 1 Zeile mit den Summen einiger Spalten. Die Ausgabe besteht aber aus 3 Zeilen den Summen der 3 Tabellen:

    $query=mysql_query("select
    YEAR(dat) as j, SUM(betrag_brutto) as suma, SUM(ust_betrag) as sumv, 0 as sumu
    from a
    union
    select YEAR(dat) as j, 0 as suma, 0 as sumv, SUM(ust_betrag) as sumu
    from b
    union
    select YEAR(dat) as j,0 as suma, 0 as sumv, SUM(sumust) as sumu,
    from c
    group by YEAR(dat)") or die(mysql_error());

    while($DS = mysql_fetch_array( $query ))
    { ...


    Ergebnis:
    suma: 2.529,88, sumv: 0,00, sumu: 356,09
    suma: 0,00, sumv: 15,00, sumu: 0,00
    suma: 0,00, sumv: 30,22, sumu: 0,00

    angestrebtes Ergebnis:
    suma: 2.529,88, sumv: 45,22, sumu: 356,09

  • ich glaube die Lösung gefunden zu haben: getestet hab ich noch nicht, ich stelle - wenn ich es zs bringe - danach die richtige Version rein
    $query=mysql_query("
    select j, sum(suma), sum(sumv), sum(sumu)
    from
    (select YEAR(dat) as j, SUM(betrag_brutto) as suma, SUM(ust_betrag) as sumv, 0 as sumu
    from a group by YEAR(dat)
    union
    select YEAR(dat) as j, 0 as suma, 0 as sumv, SUM(ust_betrag) as sumu
    from b group by YEAR(dat)
    union
    select YEAR(dat) as j,0 as suma, 0 as sumv, SUM(sumust) as sumu,
    from c group by YEAR(dat)
    ) as d
    group by j
    ") or die(mysql_error());

  • Leider ist das auch nicht die Lösung, nach wie vor 3 Zeilen, aber auch ohne die gewünschten Summen ... weiss jemand den Fehler, den es hier hat, die Konstruktion erscheint mir nämlich richtig ...?

  • Lösung einfach dargestellt:
    select j, sum (a) as sa, sum(b) as sb
    from
    (select j, xa as sa, 0 as sb
    from tab1
    union
    (select j, ya as sa, yb as sb
    from tab2)
    as tab3
    group by j

    Lösung:
    $query=mysql_query("
    select j, sum(suma) as suma, sum(sumv) as sumv, sum(sumu) as sumu
    from
    (select YEAR(dat) as j, betrag_brutto as suma, ust_betrag as sumv, 0 as sumu
    from a
    union
    select YEAR(dat) as j, 0 as suma, 0 as sumv, ust_betrag as sumu
    from b
    union
    select YEAR(dat) as j,0 as suma, 0 as sumv, sumust as sumu,
    from c
    ) as d
    group by j
    ") or die(mysql_error());