Thread: Illegal use of aggregates or non-group column in target list

I'd like to return an average for one item in my database grouped by
another item. For example,

The table looks like this:
=================
CREATE TABLE data_table (
    subject                           text,
    arm                               char,
    target                            int4,          -- end of primary
key
    reaction_time                float4
);
CREATE UNIQUE INDEX pkdata_table ON data_table (subject, arm, target);


The command I'd like to use is something like:
================================
select target, avg(reaction_time) from data_table;

and I'd like this command to return:

target    |      avg
=============
   1        |     301.3
   2        |     311.45
   3        |     299.1
   4        |     300.8


However, this statement just gives me the error: Illegal use of
aggregates or non-group column in target list.

So apparently I can only return either one or the other but not the two
together. Is there a way to get the output I want in SQL?

Thanks.
-Tony

Try :

select target, avg(reaction_time) from data_table group by target;

Haven't tested it, but it should be close to what you're looking for.

At 02:14 PM 3/3/00, G. Anthony Reina wrote:
>I'd like to return an average for one item in my database grouped by
>another item. For example,
>
>The table looks like this:
>=================
>CREATE TABLE data_table (
>     subject                           text,
>     arm                               char,
>     target                            int4,          -- end of primary
>key
>     reaction_time                float4
>);
>CREATE UNIQUE INDEX pkdata_table ON data_table (subject, arm, target);
>
>
>The command I'd like to use is something like:
>================================
>select target, avg(reaction_time) from data_table;
>
>and I'd like this command to return:
>
>target    |      avg
>=============
>    1        |     301.3
>    2        |     311.45
>    3        |     299.1
>    4        |     300.8
>
>
>However, this statement just gives me the error: Illegal use of
>aggregates or non-group column in target list.
>
>So apparently I can only return either one or the other but not the two
>together. Is there a way to get the output I want in SQL?
>
>Thanks.
>-Tony
>
>
>
>************