Обсуждение: Calculating with sql
I'm having trouble figuring out how to select something. This is the problem: 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? -- Dennis K. ~.. It is impossible to make anything foolproof, .>>. because fools are so ingenious -Roger Berg-|\
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 ) ) ASa1 Regards, Masaru Sugawara
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-|\
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