Re: Calculating with 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-|\