From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: catweazel@catweazel.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Calculating with sql
Date: Fri, 28 Jun 2002 01:50:49 +0900
Message-ID: <20020628015018.3840.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <afdeiv.214.1@kaarsemaker.nb.nu>
References: <afdeiv.214.1@kaarsemaker.nb.nu>
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
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: rk73@sea.plala.or.jp, catweazel@catweazel.org
Subject: Re: Calculating with sql
In-Reply-To: <20020628015018.3840.RK73@sea.plala.or.jp>
* 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