Most Viewed Profile Page

[mattyvx]

SQL Server version: 5.0.85

 

Table 1 : ID,Name,*..other fields which are not applicable*

Table 2 : ID,Profile,Website

_______________________________________________

 

Hi,

 

See above info for my setup. Table 1 stores member information. Table 2 stores how many times a users profile and website have been visited via my website (integers).

 

What i want to do is select a list of the top 5 viewed members - by top viewed i mean : (Profile visits + Website visits)

 

Any suggestions would be appreciated.

[ignace]

Something like this maybe

 

SELECT t1.Name, max(t2.Profile + t2.Website) as total
FROM table2 AS t2
JOIN table1 AS t1 USING (ID)
ORDER BY total DESC
LIMIT 5

[mattyvx]

I just got it to work (i think) using:

SELECT table1.ID,table1.name, SUM(table2.Profile + table2.Website) as Hits FROM table1,table2 
WHERE table1.ID=table2.ID 
ORDER BY table2.Profile DESC Limit 0,5

 

Its probably not the most efficient way though...

[ignace]

I doubt that that is the true solution all you are doing is sorting by profile + your query is the same as mine except you use sum and order by another field. Try:

 

SELECT t1.Name, sum(t2.Profile + t2.Website) as total
FROM table2 AS t2
JOIN table1 AS t1 USING (ID)
ORDER BY total DESC
LIMIT 5

[mattyvx]

It seems neither method works :S

 

Using

SELECT t1.name, sum(t2.Profile + t2.Website) as total
FROM table2 AS t2
JOIN table1 AS t1 USING (ID)
ORDER BY total DESC
LIMIT 5

 

For example if i have two members

           

Table 1

---------

ID  Name

1  John

2  Ian

---------

 

Table 2

---------

ID |  Profile  | Website

1        12          1

2          2            9

 

What im getting as a result is:

Ian (24 visits)

 

So its summing all the values and associating them with on ID...

 

Any idea?

 

What I want is

 

John (13)

Ian  (11)

 

(sorted by "total")

 

Thanks

[mattyvx]

I have solved this now

 

Remove the "sum" from the expression so its (Profile + Website) AS Total.

 

Thanks!