Обсуждение: Re: [GENERAL] trouble with sum
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...
=> 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...