Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 84BEC47702F
	for <pgsql-sql@postgresql.org>; Thu, 27 Jun 2002 14:03:33 -0400 (EDT)
Mailbox-Line: From rk73@sea.plala.or.jp  Thu Jun 27 14:03:33 2002
Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145])
	by postgresql.org (Postfix) with ESMTP id BC315476F45
	for <pgsql-sql@postgresql.org>; Thu, 27 Jun 2002 12:50:07 -0400 (EDT)
Received: from [192.168.0.4] ([218.47.117.7]) by mps2.plala.or.jp with ESMTP
	id <20020627165012.MTRH25092.mps2.plala.or.jp@[192.168.0.4]>;
	Fri, 28 Jun 2002 01:50:12 +0900
Date: Fri, 28 Jun 2002 01:50:49 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: catweazel@catweazel.org
Subject: Re: Calculating with sql
Cc: pgsql-sql@postgresql.org
In-Reply-To: <afdeiv.214.1@kaarsemaker.nb.nu>
References: <afdeiv.214.1@kaarsemaker.nb.nu>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020628015018.3840.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.05
X-Spam-Status: No, hits=0.0 required=5.0
	tests=IN_REP_TO,FROM_ENDS_IN_NUMS,DOUBLE_CAPSWORD,UPPERCASE_25_50
	version=2.30
X-Spam-Level: 
X-Archive-Number: 200206/370
X-Sequence-Number: 8335

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