Combining an inner join query and aggregate functions in SQL

Databases
1

Hi all,

Need a bit of help here fairly urgently. The RDBMS in question is SQL Server 2K.

I have two tables… In the first table (tbl06caller) there is one relevant field: SFilename.

In the second table (RtList) I have two relevant fields: Indexes and Song Name.

Indexes and SFilename contain exactly the same info.

The current (ANSI) SQL query for an inner join to display the info is

SELECT TOP 10 SFilename, [Song Name], Indexes FROM RtList INNER JOIN Tbl06caller ON RtList.Indexes = Tbl06caller.SFilename

The old SQL query I had to select the top 10 records according to popularity from RtLIst alone was (courtesy of Rudy):

SELECT TOP 10 SFilename, COUNT(*) from tbl06caller WHERE SFilename BETWEEN '10000' AND '19999' GROUP BY SFilename ORDER BY 2 desc;";

Each entry of SFilename has a Song Name attached to it (so SFilename is like a code no… 21111 = Song Name 1, 21112 = Song Name 2, 21113 = Song Name 3, etc.)

So in essence I want to produce a table like this… (without the column headings)

Song Name 1
Song Name 2
Song Name 3
Song Name 4
Song Name 5

(so only display the Song Name field in the end)

And of course, each of the songs would be ordered according to popularity (i.e. ordered according to the no. of instances tbl06caller.SFilename occurs… Which then produces the Song Name on the PHP page).

Currently I’ve tried queries like

SELECT TOP 10 SFilename, [Song Name], Indexes, COUNT(*) FROM RtList INNER JOIN Tbl06caller ON RtList.Indexes = Tbl06caller.SFilename GROUP BY tbl06caller.SFilename ORDER BY desc

But I get complaints from SQL2K saying one of the fields is not part of the aggregate function.

If anyone can help that would be great. Thanks.

2

i may have helped you before but i can’t figure out the current situation – how many of each is there? is it a 1-to-1 relationship, 1-to-many, or many-to-1?

in any case, assuming you are on the right track with your own table, a fairly safe bet, i can help youi fix your latest query to at least get rid of the error …

SELECT TOP 10
       SFilename
     , [Song Name]
     , Indexes
     , count(*)
  FROM RtList
INNER
  JOIN Tbl06caller
    ON RtList.Indexes = Tbl06caller.SFilename
GROUP
    BY tbl06caller.SFilename
     , [Song Name]
     , Indexes
ORDER
    BY count(*) desc

see, every non-aggregate in the SELECT list must be in the GROUP BY

rudy

3

Yep, that does it perfectly. Thanks again Rudy.