Group by with null values

Question

Trying to group by the result bellow:

+-------------+------+ | ID | Coln1 | Coln2 | +-------------+------+ | 10 | A | NULL | | 10 | NULL | X | | 15 | B | NULL | | 15 | NULL | Y | | 20 | C | NULL | | 20 | NULL | Z | +--------------+-----+

With the following select:

SELECT * FROM ( SELECT DB1.dbo.TABLE1.id AS ID, DB1.dbo.TABLE1.Coln1 AS Coln1, NULL AS Coln2 FROM DB1.dbo.TABLE1 UNION SELECT DB2.dbo.TABLE2.id AS ID, NULL AS Coln1, DB2.dbo.TABLE2.Coln2 AS Coln2 FROM DB2.dbo.TABLE2 ) AS A GROUP BY ID, Coln1, Coln2 ORDER BY ID ASC

Expecting this:

+-------------+------+ | ID | Coln1 | Coln2 | +-------------+------+ | 10 | A | X | | 15 | B | Y | | 20 | C | Z | +--------------+-----+

With no luck. Tried to use some HAVING with no luck neither. Thanks for any information on this.

Pரதீப் · Accepted Answer · 2014-10-31 17:44:38Z

An aggregate function should help you. Aggregate function will eliminate the NULL values.Try this

SELECT ID, max(Coln1), max(Coln2) FROM tablename GROUP BY ID

Update your query like this..

SELECT ID, Max(Coln1), Max(Coln2) FROM (SELECT DB1.dbo.TABLE1.id AS ID, DB1.dbo.TABLE1.Coln1 AS Coln1, NULL AS Coln2 FROM DB1.dbo.TABLE1 UNION SELECT DB2.dbo.TABLE2.id AS ID, NULL AS Coln1, DB2.dbo.TABLE2.Coln2 AS Coln2 FROM DB2.dbo.TABLE2) AS A GROUP BY ID ORDER BY ID ASC

Thanks. My case is more complex than the example I post. I am using UNION with some NULL columns. Gonna try your solution.
Without looking into data it is not possible to comment. According to ur example there is no need of having

Hardik Parmar · Accepted Answer · 2014-10-31 17:08:28Z

You just have to change Max function is used to select highest values & this function is very helful for avoiding for NULL values

SELECT ID, max(Coln1), max(Coln2) FROM ID, Coln1, Coln2 GROUP BY ID

Ryan B. · Accepted Answer · 2014-10-31 17:48:00Z

Why are you doing a union?

SELECT t1.id AS ID, t1.Coln1 AS Coln1, t2.Coln2 AS Coln2 FROM DB1.dbo.TABLE1 t1 JOIN DB2.dbo.TABLE2 t2 ON t1.id = t2.id

Collectives™ on Stack Overflow

Group by with null values

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related