Re: Calculating with sql

On Fri, 28 Jun 2002 01:50:49 +0900
I <rk73@sea.plala.or.jp> wrote:

> 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


I noticed that this query was better than the previous. 
Actually, it came to be nearly equal to your original.


SELECT a0.ARTIST, a0.TITLE FROM tracks AS t0, albums AS a0WHERE t0.DISCID = a0.DISCIDGROUP BY a0.DISCID, a0.ARTIST,
a0.TITLE, a0.LENGTH
 
HAVING  a0.LENGTH /COUNT(t0.TRACK)             = (SELECT max(mx.average)                    FROM (SELECT albums.LENGTH
/COUNT(tracks.TRACK)                                                  AS average                                FROM
tracks,albums                              WHERE tracks.DISCID = albums.DISCID                             GROUP BY
albums.DISCID,albums.LENGTH                              ) AS mx                 );
 
;



Regards,
Masaru Sugawara