Re: Group by bug?

2012/12/28 wd <wd(at)wdicc(dot)com>

> 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.