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ᵀᴹ

97,895
13
214
305

answered Dec 14 '11 at 11:25

Chris Date: "A 'type' that contains a null isn't a type (because types contain values). A 'tuple' that contains a null isn't a tuple (because tuples contain values)." – onedaywhen Dec 15 '11 at 10:34
@onedaywhen: well, that's the difference between theory and practice ;) – Dec 15 '11 at 10:42
My point: that's the difference between a tuple in a relation and a row in a SQL table expression. A tuple doesn't apply to SQL, both in theory and in practice. – onedaywhen Dec 15 '11 at 11:16
@onedaywhen: so you mean I should change my wording? Which word would you recommend to express combination of two (column) values in SQL then? They don't necessarily need to be from the same table nor be a complete row. – Dec 15 '11 at 11:20
I think I see what you mean. I would consider "half a row" to still be a row (the Law of Holes, I call it :) but the SQL Standard uses the term "partial row" in some contexts. "Pairs" would conveniently apply in this case! But I do know that tuple does not mean an incomplete SQL row... – onedaywhen Dec 15 '11 at 13:21
1

For example, in Tutorial D, TUPLE { a 17 , b 1 } is the same as TUPLE { b 1 , a 17 }, but in SQL the row value constructor (17, 1) is not the same as the row value constructor (1, 17). This is why your "pairs" are not tuples. Because you have omitted a row type constructor I must assume from the context they are (a, b) rather than (b, a) but its inclusion would still not make it a tuple. In contrast, TUPLE { 17 , 1 } is not a valid tuple invocation in Tutorial D, nor is TUPLE { a null , b 1 }. – onedaywhen Dec 15 '11 at 13:23

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

I have to get the reflex to think about 'level', it's very usefull! — Luc M, Jan 27 '13 at 03:55

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

2 Answers2