Search Postgresql Archives

Re: Group by bug?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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. 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux