Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id C1C49475A5E for ; Thu, 27 Jun 2002 15:38:18 -0400 (EDT) Mailbox-Line: From fake_adress@use-reply.to Thu Jun 27 15:38:18 2002 Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 9F35E476975 for ; Thu, 27 Jun 2002 15:17:01 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 462EA381CA8; Thu, 27 Jun 2002 15:16:22 -0400 (EDT) Message-ID: X-Posting-Agent: Hamster/1.3.23.4 From: Dennis Kaarsemaker X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Calculating with sql Date: Thu, 27 Jun 2002 21:15:10 +0200 Organization: catweazel.org Reply-To: catweazel@catweazel.org References: <20020628015018.3840.RK73@sea.plala.or.jp> X-Newsreader: Forte Agent 1.91/32.564 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 70 X-Complaints-To: abuse@nb.nu X-Visit: http://www.nb.nu/serv04.htm to gain access To: pgsql-sql@postgresql.org X-Spam-Status: No, hits=4.8 required=5.0 tests=DOUBLE_CAPSWORD,UPPERCASE_25_50,NO_MX_FOR_FROM version=2.30 X-Spam-Level: **** X-Archive-Number: 200206/389 X-Sequence-Number: 8354 Masaru Sugawara schreef: >On Wed, 26 Jun 2002 22:18:07 +0200 >Dennis Kaarsemaker 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- |\