trouble with sum

=> select sum(P.price), P.product_id, S.sid, S.product_id from products P,
shoppers S
-> where S.sid='0319199910043810.3.0.12'
-> group by P.product_id, S.sid;
ERROR: parser: illegal use of aggregates or non-group column in target list

no luck
i wont let me combine sum(P.price) with the other selects

this should work right?

Marcin Grondecki wrote:

select sum(p.price), p.product_id, s.sid from ...
where ...
group by p.product_id, s.sid

"group by" clause is the key. You cannot use non-aggregate fields
in question like this, where some fields are aggregates, without grouping
them.
Sometimes i'd like to use syntax you presented, but in postgreSQL
it isn't possible :( And it makes some sense...

At 13:29 99-03-19 -0500, you wrote:

select sum(P.price), P.product_id, S.sid, S.product_id from products P,
shoppers S
where S.sid='0319199910043810.3.0.12' and P.product_id= S.product_id;

i've used similar commands like this one with other engines with no
trouble

what do i need to do to get it to work with postgre

Marcin Grondecki
ojciec@mtl.pl
+48(604)468725
***** I'm not a complete idiot, some parts are missing...

Should work :)
In group by you should group by ALL non-aggregate fields,
in this example - s.product_id too.
But in your original question you wrote "where s.product_id=p.product_id",
so one of these fields became redundant.

At 14:16 99-03-19 -0500, pete collins wrote:

=> select sum(P.price), P.product_id, S.sid, S.product_id from products P,
shoppers S
-> where S.sid='0319199910043810.3.0.12'
-> group by P.product_id, S.sid;
ERROR: parser: illegal use of aggregates or non-group column in target list

no luck
i wont let me combine sum(P.price) with the other selects

this should work right?

Marcin Grondecki
ojciec@mtl.pl
+48(604)468725
***** I'm not a complete idiot, some parts are missing...