SQL Select SUM question

[Ruslan Zasukhin]

On 9/27/06 1:17 PM, "Dave Addey" <listmail1 at dsl.pipex.com> wrote:

Hi Dave,
 
> I have an SQL question for V4RB 2.4.2. I¹m trying to find the best way to
> add summing to my queries.
> 
> I have a table of music Tracks, a table of Playlists, and a PlaylistTracks
> table which joins the two. Each Track has a UniqueID, each Playlist has a
> UniqueID, and the PlaylistTracks table links them together with two Foreign
> Key links (VLink).
> 
> I currently do this (for example):
> 
> SELECT * FROM Tracks, PlaylistTracks WHERE PlaylistTracks.TrackUniqueIDPtr =
> Tracks.UniqueID AND PlaylistTracks.PlaylistUniqueIDPtr = 6

TIP:

    SELECT * is not very safe, because if future you change
    fields in tables your old code can be broken.
 
> (...to select information about all tracks from playlist 6).
> 
> What I want to do now is to also calculate the total duration of all
> selected tracks.  I have a VDouble field in the Tracks table, called
> Tracks.Duration, and so I would expect to do something like
> SUM(Tracks.Duration).  I don¹t want to have to do two SELECT queries, as it
> seems a shame to have to do the join twice.
> 
> So should I do something like:
> 
> SELECT Tracks.*, SUM(Tracks.Duration) FROM Tracks, PlaylistTracks WHERE
> PlaylistTracks.TrackUniqueIDPtr = Tracks.UniqueID AND
> PlaylistTracks.PlaylistUniqueIDPtr = 6
> 
> This gives a database error:
> 
> ³In the SELECT clause you an use only fields listed in the GROUP BY and
> aggregate functions.²

Right. SQL Standard
 
> Can I do the general ³SELECT Tracks.*², and also the ³SELECT SUM()², as part
> of the same query?  If so:

no
 
> How should I word my SQL to do this?

you need list in SELECT all fields you want
Plus all of them should present in GROUP BY

SELECT t.f1, t.f2, t.f3, SUM(Tracks.Duration)
FROM Tracks t, PlaylistTracks
WHERE...
GROUP BY t.f1, t.f2, t.f3

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]