Thread: GROUPING
It's been a while since I used postgresql but today I have converted one of my web apps but with one small problem. I goto do a group as designed and executed in mysql and I get told that this and this must be part of the aggreate etc I am puzzled and wonder if someone could bring me up to stratch with grouping in postgresql this is my current sql: SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; Result: Attribute telemetry.rpvuid must be GROUPed or used in an aggregate function Oh then if I include rpvuid I get you must include this field and on it goes. Thanks... Timothy J Hitchens tim@hitcho.com.au
On Sat, 13 Oct 2001, Timothy J Hitchens wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > > Result: > > Attribute telemetry.rpvuid must be GROUPed or used in an aggregate > function > > > Oh then if I include rpvuid I get you must include this field and on it > goes. > > Thanks... Well, that query doesn't necessarily return consistant results. Let's say you have a table a | b ----- 1 | 1 1 | 2 2 | 3 What should select * from table group by a; give? There are two values of b that could be chosen for that group. I believe the appropriate part of the spec is from query specification (7.9 of my draft) 7) If T is a grouped table, then each <column reference> in each <value expression> that references a column of T shall refer- ence a grouping column or be specified within a <set function specification>....
On Sat, 13 Oct 2001 03:32:57 +0000 (UTC), <tim@hitcho.com.au> wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > I seem to recall reading something recently showing that mysql does something completely different with GROUP from what postgres does. Stephan posted a nice description of how GROUP works here. It might help if we knew what you want the result of your query to be.
On Sat, 13 Oct 2001, Timothy J Hitchens wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > > Result: > > Attribute telemetry.rpvuid must be GROUPed or used in an aggregate > function > > > Oh then if I include rpvuid I get you must include this field and on it > goes. Normally, GROUP BY is used to group up records to look at an aggregate. For example, if you have this table of your friends: CREATE TABLE friends ( friend TEXT, country_code CHAR(2), income FLOAT, ); I could get a count of how many friends lived in each country by: SELECT country_code, COUNT(*) FROM friends GROUP BY country_code; Or I could get the average amount of money made by friends in each country with: SELECT country_code, avg(income) FROM friends GROUP BY country_code; In other words, when you GROUP BY, you're looking for an aggregate (a function that is applied to a group and returns a single value, such as average, minimum, maximum, count, etc.) Can you be more specific about what you're actually trying to accomplish?
On Sat, 13 Oct 2001, Timothy J Hitchens wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > You should write: SELECT name_1 FROM telemetry where beat > 12 GROUP BY name_1 because, you must indicate on "SELECT name_1" the attribute name for GROUP it . did you understand? byeEsteban Gutierrez.egutierr@alumnos.ubiobio.cl > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >