pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feederr: select f() from i where (f()) in (select f() from x group by j);
2+ messages / 2 participants
[nested] [flat]
* err: select f() from i where (f()) in (select f() from x group by j);
@ 1999-08-14 00:13 Chad Miller <cmiller+ps@surfsouth.com>
1999-08-15 13:40 ` Re: [SQL] err: select f() from i where (f()) in (select f() from x group by j); Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 2+ messages in thread
From: Chad Miller @ 1999-08-14 00:13 UTC (permalink / raw)
To: pgsql-sql
Hi there. Please forgive my igmorance.
I have a table populated with lots of numbers, names, and timestamps. I'd
like to find the max of the sums of the numbers and the timestamp.
That is, find the timestamp and the sum of the numbers when the sum of
the numbers is max. Yow!
This does half -- the summing:
> select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp;
If I create a view, with
> create view foo as select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp;
I get:
Table = foo
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| timestamp | datetime | 8 |
| sum | int2 | 2 |
+----------------------------------+----------------------------------+-------+
(Note the fields' naming) Still -- I'd like a more elegant way to do this,
than creating a view.
I came up with:
> select timestamp, max(sum) from timelines where (timestamp, sum) in (select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp);
...which returns
< ERROR: attribute 'sum' not found
The corresponding column in the view was titled ``sum.'' Hmmm. So, perhaps
fully writing the ``sum(num)'' is correct.
> select timestamp from timelines where (timestamp, sum(num)) in (select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp);
...which returns
< ERROR: parser: illegal use of aggregates or non-group column in target list
Or...
> select timestamp, max(sum(num)) from timelines where (timestamp, sum(num)) in (select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp);
...which returns
< ERROR: type id lookup of 223198728 failed
This is making me itch. Advice? Wizardry? Comments? Flames?
- chad
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] err: select f() from i where (f()) in (select f() from x group by j);
1999-08-14 00:13 err: select f() from i where (f()) in (select f() from x group by j); Chad Miller <cmiller+ps@surfsouth.com>
@ 1999-08-15 13:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 2+ messages in thread
From: Herouth Maoz @ 1999-08-15 13:40 UTC (permalink / raw)
To: Chad Miller <cmiller+ps@surfsouth.com>; pgsql-sql
At 03:13 +0300 on 14/08/1999, Chad Miller wrote:
> If I create a view, with
>
> > create view foo as select timestamp, sum(num) from timelines where
>(name='foo' or name='bar' or name='baz') group by timestamp;
>
> I get:
> Table = foo
> +-----------------------------+----------------------------------+-------+
> | Field | Type | Length|
> +-----------------------------+----------------------------------+-------+
> | timestamp | datetime | 8 |
> | sum | int2 | 2 |
> +-----------------------------+----------------------------------+-------+
>
> (Note the fields' naming) Still -- I'd like a more elegant way to do this,
> than creating a view.
>
> I came up with:
>
> > select timestamp, max(sum) from timelines where (timestamp, sum) in
>(select timestamp, sum(num) from timelines where (name='foo' or name='bar'
>or name='baz') group by timestamp);
>
> ...which returns
>
> < ERROR: attribute 'sum' not found
The way to get rid of names that would make your life hard is to put field
aliases in the CREATE VIEW:
create view foo as select timestamp as ts_col, sum(num) as sum_col
from timelines, where ....;
Anyway, it's not the problem here. The problem is that you selected from
timelines instead of from foo.
As for a more elegant way of doing the same task, I'm not entirely sure,
because I don't have the latest PostgreSQL here, but here is a general idea:
SELECT timestamp, sum( num ) as the_sum
FROM timelines
WHERE (name='foo' or name='bar' or name='baz')
GROUP BY timestamp
ORDER BY the_sum DESC
LIMIT 1;
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-08-15 13:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-14 00:13 err: select f() from i where (f()) in (select f() from x group by j); Chad Miller <cmiller+ps@surfsouth.com>
1999-08-15 13:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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