pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGROUPING
5+ messages / 5 participants
[nested] [flat]
* GROUPING
@ 2001-10-13 01:15 Timothy J Hitchens <tim@hitcho.com.au>
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 05:26 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Timothy J Hitchens <tim@hitcho.com.au>
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 21:51 Lee Harr <missive@frontiernet.net>
parent: Timothy J Hitchens <tim@hitcho.com.au>
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-14 15:34 Joel Burton <joel@joelburton.com>
parent: Timothy J Hitchens <tim@hitcho.com.au>
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-20 18:05 Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>
parent: Timothy J Hitchens <tim@hitcho.com.au>
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