pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCalculating with sql
4+ messages / 2 participants
[nested] [flat]
* Calculating with sql
@ 2002-06-26 20:18 Dennis Kaarsemaker <fake_adress@use-reply.to>
2002-06-27 16:50 ` Re: Calculating with sql Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 1 reply; 4+ messages in thread
From: Dennis Kaarsemaker @ 2002-06-26 20:18 UTC (permalink / raw)
To: pgsql-sql
I'm having trouble figuring out how to select something.
This is the problem:
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Calculating with sql
2002-06-26 20:18 Calculating with sql Dennis Kaarsemaker <fake_adress@use-reply.to>
@ 2002-06-27 16:50 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-06-27 19:15 ` Re: Calculating with sql Dennis Kaarsemaker <fake_adress@use-reply.to>
2002-06-28 14:10 ` Re: Calculating with sql Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 2 replies; 4+ messages in thread
From: Masaru Sugawara @ 2002-06-27 16:50 UTC (permalink / raw)
To: catweazel@catweazel.org; +Cc: pgsql-sql
On Wed, 26 Jun 2002 22:18:07 +0200
Dennis Kaarsemaker <fake_adress@use-reply.to> wrote:
> From a copy of the CDDB-database i want to select the artist & album that
> has on average the longest tracks.
>
> But in the tracks table there is no such field as length, so i have to
> calculate it. But when i try to do so it just gives me errors.
>
> This is the erroneous query i have made so far
>
> select albums.ARTIST, albums.TITLE from tracks,albums
> where tracks.DISCID = albums.DISCID
> group by tracks.DISCID
> having(albums.LENGTH/count(tracks.TRACK)) =
> (
> select max(albums.LENGTH/count(tracks.TRACK)) from tracks,albums
> where tracks.DISCID = albums.DISCID
> group by tracks.DISCID
> );
>
> What is the correct way of selecting the album?
As for this query, some columns, AERIST, TITLE, and LENGTH, are lacking
at the GROUP BY clauses. Probably, I would think a nearly correct one is
something like the following.
SELECT
a1.ARTIST, a1.TITLE
FROM
(SELECT a0.DISCID, a0.ARTIST, a0.TITLE
FROM tracks AS t0, albums AS a0
WHERE t0.DISCID = a0.DISCID
GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH
HAVING a0.LENGTH /COUNT(t0.TRACK)
= (SELECT max(mx.average)
FROM (SELECT albums.DISCID,
albums.LENGTH /COUNT(tracks.TRACK)
AS average
FROM tracks, albums
WHERE tracks.DISCID = albums.DISCID
GROUP BY albums.DISCID, albums.LENGTH
) AS mx
)
) AS a1
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Calculating with sql
2002-06-26 20:18 Calculating with sql Dennis Kaarsemaker <fake_adress@use-reply.to>
2002-06-27 16:50 ` Re: Calculating with sql Masaru Sugawara <rk73@sea.plala.or.jp>
@ 2002-06-27 19:15 ` Dennis Kaarsemaker <fake_adress@use-reply.to>
1 sibling, 0 replies; 4+ messages in thread
From: Dennis Kaarsemaker @ 2002-06-27 19:15 UTC (permalink / raw)
To: pgsql-sql
Masaru Sugawara schreef:
>On Wed, 26 Jun 2002 22:18:07 +0200
>Dennis Kaarsemaker <fake_adress@use-reply.to> wrote:
>
>> From a copy of the CDDB-database i want to select the artist & album that
>> has on average the longest tracks.
>>
>> But in the tracks table there is no such field as length, so i have to
>> calculate it. But when i try to do so it just gives me errors.
>>
>> This is the erroneous query i have made so far
>>
>> select albums.ARTIST, albums.TITLE from tracks,albums
>> where tracks.DISCID = albums.DISCID
>> group by tracks.DISCID
>> having(albums.LENGTH/count(tracks.TRACK)) =
>> (
>> select max(albums.LENGTH/count(tracks.TRACK)) from tracks,albums
>> where tracks.DISCID = albums.DISCID
>> group by tracks.DISCID
>> );
>>
>> What is the correct way of selecting the album?
>
>
>As for this query, some columns, AERIST, TITLE, and LENGTH, are lacking
>at the GROUP BY clauses. Probably, I would think a nearly correct one is
>something like the following.
>
>
>SELECT
> a1.ARTIST, a1.TITLE
>FROM
> (SELECT a0.DISCID, a0.ARTIST, a0.TITLE
> FROM tracks AS t0, albums AS a0
> WHERE t0.DISCID = a0.DISCID
> GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH
> HAVING a0.LENGTH /COUNT(t0.TRACK)
> = (SELECT max(mx.average)
> FROM (SELECT albums.DISCID,
> albums.LENGTH /COUNT(tracks.TRACK)
> AS average
> FROM tracks, albums
> WHERE tracks.DISCID = albums.DISCID
> GROUP BY albums.DISCID, albums.LENGTH
> ) AS mx
> )
> ) AS a1
>
>
>
>Regards,
>Masaru Sugawara
Thank you very much!
The qury gives no syntax errors now (except for a few 'as'es (which i now
have removed)
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
No, but i'll certainly do, thanks again.
--
Dennis K.
~.. It is impossible to make anything foolproof,
.>>. because fools are so ingenious -Roger Berg-
|\
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Calculating with sql
2002-06-26 20:18 Calculating with sql Dennis Kaarsemaker <fake_adress@use-reply.to>
2002-06-27 16:50 ` Re: Calculating with sql Masaru Sugawara <rk73@sea.plala.or.jp>
@ 2002-06-28 14:10 ` Masaru Sugawara <rk73@sea.plala.or.jp>
1 sibling, 0 replies; 4+ messages in thread
From: Masaru Sugawara @ 2002-06-28 14:10 UTC (permalink / raw)
To: Masaru Sugawara <rk73@sea.plala.or.jp>; +Cc: pgsql-sql
On Fri, 28 Jun 2002 01:50:49 +0900
I <rk73@sea.plala.or.jp> wrote:
> SELECT
> a1.ARTIST, a1.TITLE
> FROM
> (SELECT a0.DISCID, a0.ARTIST, a0.TITLE
> FROM tracks AS t0, albums AS a0
> WHERE t0.DISCID = a0.DISCID
> GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH
> HAVING a0.LENGTH /COUNT(t0.TRACK)
> = (SELECT max(mx.average)
> FROM (SELECT albums.DISCID,
> albums.LENGTH /COUNT(tracks.TRACK)
> AS average
> FROM tracks, albums
> WHERE tracks.DISCID = albums.DISCID
> GROUP BY albums.DISCID, albums.LENGTH
> ) AS mx
> )
> ) AS a1
I noticed that this query was better than the previous.
Actually, it came to be nearly equal to your original.
SELECT a0.ARTIST, a0.TITLE
FROM tracks AS t0, albums AS a0
WHERE t0.DISCID = a0.DISCID
GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH
HAVING a0.LENGTH /COUNT(t0.TRACK)
= (SELECT max(mx.average)
FROM (SELECT albums.LENGTH /COUNT(tracks.TRACK)
AS average
FROM tracks, albums
WHERE tracks.DISCID = albums.DISCID
GROUP BY albums.DISCID, albums.LENGTH
) AS mx
);
;
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-06-28 14:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-26 20:18 Calculating with sql Dennis Kaarsemaker <fake_adress@use-reply.to>
2002-06-27 16:50 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-06-27 19:15 ` Dennis Kaarsemaker <fake_adress@use-reply.to>
2002-06-28 14:10 ` Masaru Sugawara <rk73@sea.plala.or.jp>
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