Re: UNION mit NULL-Werten
Hallo Jörg,
mal ein paar Vorschläge dazu. Vorsicht, Du fällst mal wieder in die GROUP BY Falle.
/* NULL Tupel entfernen */
SELECT * FROM ( SELECT name, SUM(value) AS sum_value
FROM t
WHERE YEAR(datum)=2011
GROUP BY name
UNION
SELECT name, SUM(value) AS sum_value
FROM t
WHERE YEAR(datum)=2012
GROUP BY name
) u
WHERE u.name IS NOT NULL
AND u.sum_value IS NOT NULL;
+------+-----------+
| name | sum_value |
+------+-----------+
| Bill | 20 |
+------+-----------+
1 row in set (0.00 sec)
mysql>
/* Name immer vorhanden */
SELECT u.name, SUM(u.Vorjahr), SUM(u.Heuer)
FROM ( SELECT name, SUM(value) AS Vorjahr, 0 AS Heuer
FROM t
WHERE YEAR(datum)=2011
GROUP BY name
UNION
SELECT name, 0 AS Vorjahr, SUM(value) AS Heuer
FROM t
WHERE YEAR(datum)=2012
GROUP BY name
) AS u
GROUP BY u.name;
+------+----------------+--------------+
| name | SUM(u.Vorjahr) | SUM(u.Heuer) |
+------+----------------+--------------+
| Bill | 0 | 20 |
+------+----------------+--------------+
1 row in set (0.00 sec)
mysql>
EDIT...Code formatierung korrigert
Grüße
Thomas
Edited 1 time(s). Last edit at 08/06/2012 01:50PM by Thomas Wiedmann.
Subject
Views
Written By
Posted
1720
August 05, 2012 04:30PM
Re: UNION mit NULL-Werten
987
August 06, 2012 10:32AM
792
August 06, 2012 12:54PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.