Zapytanie MySQL do rankingu zdjęć

Witam.

Mam 2 tabele:

CREATE TABLE `psPhotosRating` (
  `id_photo_rating` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_uploaded_files` int(11) NOT NULL,
  `rating` int(2) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



CREATE TABLE `psUploadedFiles2` (
  `id_uploaded_files` int(10) UNSIGNED NOT NULL,
  `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `id_user` int(11) NOT NULL DEFAULT '0',
  `file_path` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `file_name` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `category` bigint(20) NOT NULL DEFAULT '0',
  `tags` text COLLATE utf8_unicode_ci,
  `description` mediumtext COLLATE utf8_unicode_ci,
  `promo_in_front` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `count` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `psPhotosRating`
  ADD PRIMARY KEY (`id_photo_rating`);


ALTER TABLE `psPhotosRating`
  MODIFY `id_photo_rating` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `psUploadedFiles2`
  MODIFY `id_uploaded_files` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

psUploadedFiles2 - tabela jest bazą danych zdjęć
psPhotosRating - tabla z głosami oddanymi na psUploadedFiles2 psUploadedFiles2

Nie każdy obrazek ma oddane na niego głosy.

Potrzebuję zapytanie SQL wyświetlające listę zdjęć (psUploadedFiles2) posortowanych po rankingu z psPhotosRating (średniej głosów).
średnia głosów = ilość głosów / wartość głosów (psPhotosRating => rating)

Wie ktoś może jak ułożyć takie zapytanie?

Ja wiem.
Ale czy ty wiesz, jak chcesz obliczać rating?
Np. zdjęcie z 99 głosami na 5 i jednym na 4 wg ciebie jest niżej w ratingu niż zdjęcie z jednym głosem na 5.

Chciałbym żeby ranking był liczony jako suma głosów oddanych na zdjęcie (psPhotosRating -> suma rating)) / ilość głosów oddanych na dane zdjęcie.
Wynik miałby być posortowany według wartości uzyskanej w powyższym dzieleniu.

W tabeli psPhotosRating mam przykładowo rekordy:

INSERT INTO `psPhotosRating` (`id_photo_rating`, `id_user`, `id_uploaded_files`, `rating`, `timestamp`) VALUES
(1, 36, 11, 4, '2019-02-06 09:09:08'),
(2, 112, 5, 5, '2019-02-06 09:54:34'),
(3, 16, 11, 1, '2019-02-04 11:20:24');

Użyj AVG

Myślisz o czymś takim:

SELECT (SELECT CONCAT(file_path, '/', file_name) AS image FROM psUploadedFiles B WHERE category = user.id_user and enum =3  ORDER by number ASC LIMIT 1) AS image2, user.username, user.url_address, files.*, CONCAT(file_path, '/', file_name) AS image, (SELECT COUNT(id_photo_rating) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) as numbers, (SELECT SUM(rating)  FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) as summary, (SELECT ROUND(AVG(rating),2) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) as avgratting FROM psPhotosRating photos JOIN psUploadedFiles2 files ON files.id_uploaded_files = photos.id_uploaded_files JOIN psUser user ON user.id_user = files.id_user GROUP BY id_uploaded_files ORDER BY avgratting DESC;

SELECT 
    (SELECT 
            CONCAT(file_path, '/', file_name) AS image
        FROM
            psUploadedFiles B
        WHERE
            category = USER.id_user AND enum = 3
        ORDER BY NUMBER ASC
        LIMIT 1) AS image2,
    USER.username,
    USER.url_address,
    files.*,
    CONCAT(file_path, '/', file_name) AS image,
    (SELECT 
            COUNT(id_photo_rating)
        FROM
            psPhotosRating
        WHERE
            id_uploaded_files = photos.id_uploaded_files) AS numbers,
    (SELECT 
            SUM(rating)
        FROM
            psPhotosRating
        WHERE
            id_uploaded_files = photos.id_uploaded_files) AS summary,
    (SELECT 
            ROUND(AVG(rating), 2)
        FROM
            psPhotosRating
        WHERE
            id_uploaded_files = photos.id_uploaded_files) AS avgratting
FROM
    psPhotosRating photos
        JOIN
    psUploadedFiles2 files ON files.id_uploaded_files = photos.id_uploaded_files
        JOIN
    psUser USER ON USER.id_user = files.id_user
GROUP BY id_uploaded_files
ORDER BY avgratting DESC;

znajdź różnicę...
Ale nie, nie myślałem o czymś takim, choć użyłeś AVG.

To ja jeszcze dodam do @Marcin.Miga mała cegiełke do czytelności kodu:

SELECT 
    (SELECT 
            CONCAT(file_path, '/', file_name) AS image
        FROM
            psUploadedFiles B
        WHERE
            category = USER.id_user AND enum = 3
        ORDER BY NUMBER ASC
        LIMIT 1) AS image2,
    USER.username,
    USER.url_address,
    files.*,
    CONCAT(file_path, '/', file_name) AS image,
    numbers,
    summary,
    avgratting
FROM
    psPhotosRating photos
    JOIN psUploadedFiles2 files ON files.id_uploaded_files = photos.id_uploaded_files
    JOIN psUser USER ON USER.id_user = files.id_user
	JOIN (SELECT 
			id_uploaded_files ID
            ,ROUND(AVG(rating), 2) avgratting
			,SUM(rating) summary
			,COUNT(id_photo_rating) numbers
        FROM
            psPhotosRating
        GROUP BY
            id_uploaded_files) psPR on psPR.ID = photos.id_uploaded_files
GROUP BY 
	id_uploaded_files
ORDER BY 
	avgratting DESC