Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

Question

I have been getting myself very confused.

Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE?

My guess is that I would use it if I wanted to conditionally group a set of data by column B (if B was not null) and by column A otherwise. Does that sound right?

score 37 · Accepted Answer · edited Dec 14 '11 at 21:28

37

With GROUP BY b,a the tuples (null, 1), (1,1), (2,1) and (17,1) would end up in four different groups.

With GROUP BY coalesce(b,a) the tuples (null,1), (1,1), (2,1) and (17,1) would end up in the same group.

If you want the "conditional" grouping, then yes, the version with coalesce is probably what you want.

edited Dec 14 '11 at 21:28

ypercubeᵀᴹ

99,450
13
217
306

answered Dec 14 '11 at 11:25

score 16 · Answer 2 · answered Dec 14 '11 at 14:58

Here is a demonstration of a_horse_with_no_name's excellent⁺¹ answer.

SQL> WITH Data AS (
  2     SELECT level, DECODE(Level,3,NULL,1) A
  3        , DECODE(level,2,NULL,4,2,1) B
  4     FROM dual connect by level <=5
  5     )
  6  SELECT A, B, count(*) FROM Data GROUP BY B, A;

A B   COUNT(*)
- - ----------
1 1          2
1            1
1 2          1
  1          1


SQL> WITH Data AS (
  2     SELECT level, DECODE(Level,3,NULL,1) A
  3        , DECODE(level,2,NULL,4,2,1) B
  4     FROM dual connect by level <=5
  5     )
  6  SELECT COALESCE(B, A) X, count(*) FROM Data GROUP BY COALESCE(B, A);

X   COUNT(*)
- ----------
1          4
2          1

Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

2 Answers2