From: Dennis Kaarsemaker <fake_adress@use-reply.to>
To: pgsql-sql@postgresql.org
Subject: Re: Calculating with sql
Date: Thu, 27 Jun 2002 21:15:10 +0200
Message-ID: <affv8u.2co.1@kaarsemaker.nb.nu> (raw)
References: <20020628015018.3840.RK73@sea.plala.or.jp>
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-
|\
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fake_adress@use-reply.to
Subject: Re: Calculating with sql
In-Reply-To: <affv8u.2co.1@kaarsemaker.nb.nu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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