MySQL Forums
Forum List  »  German

Re: UNION mit NULL-Werten
Posted by: Thomas Wiedmann
Date: August 06, 2012 10:32AM

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.

Options: ReplyQuote


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.