Rank by Views with MySQL

Question

I love MySQL and I have been using it during the last 2 years. But now I'm stuck...

I have three tables

 Files
---------
id | name  | views | folder_id
1  | car   | 274   |   1
2  | bike  | 100   |   1
3  | train | 120   |   2

 Folders
---------
id | name    | storage_id
1  | folder1 |     1
2  | folder2 |     2

 Storage
---------
id | name
1  | storage1
2  | storage2
3  | storage3

I would like to make a mysql query, so that the Storage's table is ordered by the total amount of views of all the files inside each folder which is inside a storage.

My first approach is to add the total views of each file grouping by the folder_id but then, how can I pass that value to the folders' table to then order the storage's table?

I'm sorry for my poor explanation. I'm still a noob.

The idea is to order de storage's table by the total amount of views of the files

Thanks in advance

score 1 · Accepted Answer · answered Feb 22 '14 at 00:09

Join the tables, group by Storage and sort by the total number of file views:

SELECT      Storage.*
FROM        Storage
  LEFT JOIN Folders ON Folders.storage_id = Storage.id
  LEFT JOIN Files   ON Files.folder_id    = Folders.id
GROUP BY    Storage.id
ORDER BY    SUM(Files.views) DESC

See it on sqlfiddle.

score 1 · Answer 2 · answered Feb 22 '14 at 00:17

A slightly different query to eggyal's that shows the totals too, in case you wanted it

 SELECT Storage.name, SUM(Files.views) AS total_views FROM Storage
 LEFT JOIN Folders ON Folders.storage_id = Storage.id
 LEFT JOIN Files ON Files.folder_id = Folders.id
 GROUP BY Storage.name
 ORDER BY total_views DESC

Rank by Views with MySQL

2 Answers2