Re: Calculating with sql

On Fri, 28 Jun 2002 01:50:49 +0900
I <rk73(at)sea(dot)plala(dot)or(dot)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 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.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