pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
GROUPING
5+ messages / 5 participants
[nested] [flat]

* GROUPING
@ 2001-10-13 01:15 Timothy J Hitchens <tim@hitcho.com.au>
  2001-10-13 05:26 ` Re: GROUPING Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-10-13 21:51 ` Re: GROUPING Lee Harr <missive@frontiernet.net>
  2001-10-14 15:34 ` Re: GROUPING Joel Burton <joel@joelburton.com>
  2001-10-20 18:05 ` Re: GROUPING Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>
  0 siblings, 4 replies; 5+ messages in thread

From: Timothy J Hitchens @ 2001-10-13 01:15 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: GROUPING
  2001-10-13 01:15 GROUPING Timothy J Hitchens <tim@hitcho.com.au>
@ 2001-10-13 05:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2001-10-13 05:26 UTC (permalink / raw)
  To: Timothy J Hitchens <tim@hitcho.com.au>; +Cc: pgsql-sql

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>....




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: GROUPING
  2001-10-13 01:15 GROUPING Timothy J Hitchens <tim@hitcho.com.au>
@ 2001-10-13 21:51 ` Lee Harr <missive@frontiernet.net>
  3 siblings, 0 replies; 5+ messages in thread

From: missive@frontiernet.net @ 2001-10-13 21:51 UTC (permalink / raw)
  To: pgsql-sql

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.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: GROUPING
  2001-10-13 01:15 GROUPING Timothy J Hitchens <tim@hitcho.com.au>
@ 2001-10-14 15:34 ` Joel Burton <joel@joelburton.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Joel Burton @ 2001-10-14 15:34 UTC (permalink / raw)
  To: Timothy J Hitchens <tim@hitcho.com.au>; +Cc: pgsql-sql

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?





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: GROUPING
  2001-10-13 01:15 GROUPING Timothy J Hitchens <tim@hitcho.com.au>
@ 2001-10-20 18:05 ` Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>
  3 siblings, 0 replies; 5+ messages in thread

From: Esteban Gutierrez Abarzua @ 2001-10-20 18:05 UTC (permalink / raw)
  To: Timothy J Hitchens <tim@hitcho.com.au>; +Cc: pgsql-sql

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?

bye
 
 Esteban Gutierrez.
 egutierr@alumnos.ubiobio.cl

  


> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-10-20 18:05 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-13 01:15 GROUPING Timothy J Hitchens <tim@hitcho.com.au>
2001-10-13 05:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-13 21:51 ` Lee Harr <missive@frontiernet.net>
2001-10-14 15:34 ` Joel Burton <joel@joelburton.com>
2001-10-20 18:05 ` Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox