CODEWITHSUNDEEP
sqlsql-server
Khrys
Khrys

Reputation: 2774

Group by with null values

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.

Upvotes: 0

Views: 97

Answers (3)

Ryan B.
Ryan B.

Reputation: 3665

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

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 3

Hardik Parmar
Hardik Parmar

Reputation: 1051

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

Upvotes: 1

Related Questions