Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Group By a nullable column

Tags:

sql

oracle

As you know Oracle treats NULLs as one "value" in GROUP BY queries. Is there a workaround solution to group data treating NULLs as different values each. For example:

table t:

colA     colB
 A         1 
 A         5 
<null>     3 
<null>     2

select colA, min(colB) from t group by colA returns:

colA     colB
 A         1 
<null>     2

but i want a query to return:

colA     colB
 A         1 
<null>     3 
<null>     2

Thanks in advance

like image 886
Oguz Kurt Avatar asked Nov 26 '12 13:11

Oguz Kurt


Video Answer


2 Answers

SELECT colA, MIN(colB)
FROM t
WHERE colA IS NOT NULL
GROUP BY colA
UNION ALL
SELECT colA, colB
FROM t
WHERE colA IS NULL
like image 182
lc. Avatar answered Sep 28 '22 01:09

lc.


@lc's answer is a great one, but just for the sport here is another solution:

SELECT  colA, min(colB)
FROM t
GROUP BY nvl(colA, rownum),colA

And here is a sqlfiddle demo

like image 22
A.B.Cade Avatar answered Sep 28 '22 02:09

A.B.Cade