SQL Select SUM question

[Dave Addey]

Hi All,

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

(...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.²

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

How should I word my SQL to do this?

How do I refer to the output of the ³SELECT SUM()²?  I assume I get one
Vcursor from the SELECT.

I¹m running the SQL via VDatabase.SqlSelect(SQLText,
EVCursorLocation.kServerSide, EVLockType.kNoLocks,
EVCursorDirection.kRandom) using V4RB 2.4.2.

Any help much appreciated!

Dave.