Re: Group by bug?

[wd <wd@xxxxxxxxx>]

Oh, I see, thanks for your quick reply.

On Fri, Dec 28, 2012 at 3:47 PM, Jov <zhao6014@xxxxxxxxx> wrote:

2012/12/28 wd <wd@xxxxxxxxx>

hi,

wd_test=# \d t1
                         Table "public.t1"
 Column |  Type   |                    Modifiers                   
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t1_id_seq'::regclass)
 tag    | text    |

wd_test=# select * from t1;
 id | tag
----+-----
  1 | a
  2 | a
  3 | b
  4 | c
  5 | b
(5 rows)

---- this sql will not group by the case result.

wd_test=# select case t1.tag when 'a' then '1' else '0' end as tag, count(*) from t1 group by tag;

here the group by key tag is t1.tag,not the tag int the select list

 

 tag | count
-----+-------
 0   |     1
 0   |     2
 1   |     2
(3 rows)

---- this sql will group by the case result.
wd_test=# select case t1.tag when 'a' then '1' else '0' end as ttag, count(*) from t1 group by ttag;

here the ttag is the select list ttag,it is equal with group by 1.

 

 ttag | count
------+-------
 0    |     3
 1    |     2
(2 rows)

http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-GROUPBY

 

GROUP BY will condense into a single row all selected rows that share the same values for the grouped expressions. _expression_ can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary _expression_ formed from input-column values. In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.

so it is not a bug.