How to count and order correctly result from table

I have like this task currently:

CREATE TABLE IF NOT EXISTS `files` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_user` INT(11) DEFAULT NULL,
  `flag_converted` TINYINT(1) DEFAULT '0' COMMENT '0 - not converted, 1 - converted',
  `flag_error` TINYINT(1) DEFAULT '0' COMMENT '0 - no errors, 1 - error occurred',
  `rating` INT(11) UNSIGNED DEFAULT NULL COMMENT '1-5',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;


INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 0, 0, NULL);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 1, 0, 5);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 1, 0, 5);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (NULL, 0, 1, 2);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 0, 0, NULL);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 1, 0, 5);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (1, 0, 1, 2);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (2, 0, 0, NULL);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (3, 1, 0, 5);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (3, 1, 0, 5);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);
INSERT INTO `files` (`id_user`, `flag_converted`, `flag_error`, `rating`) VALUES (4, 0, 1, 2);

(sql fiddle: http://sqlfiddle.com/#!9/431723/49/0)

It is necessary to form a SQL query to the files table which displays the number of files based on the success of the conversion and the presence of a good rating from registered / unregistered users.

Output format:

user_type (‘Visitor’ if id_user = NULL; ‘User’ otherwise),

flag_converted,

is_best_rating (‘1’ – if rating = 5; ‘0’ otherwise),

count (number of records).

The results must be sorted by count and is_best_rating in reverse order. And display only groups where count is greater than 1.

My current query:

SELECT id, flag_converted, CASE
    WHEN rating = 5 THEN '1'
    ELSE '0'
END AS is_best_rating, CASE
    WHEN id_user IS NULL THEN 'Visitor'
    ELSE 'User'
END AS user_type from files

In my task says:

The results must be sorted by count

Where I can get value of count correctly in this case and sort?

Answer

If I understand correctly, according to the task requirement, you’ll need to return 4 columns user_type,flag_converted,is_best_rating and count with specific CASE or IF condition on the id_user and rating. Your current query seems to met most of those conditions but let’s try to follow the tasks requirement. First, change the columns in SELECT to:

SELECT 
CASE
    WHEN id_user IS NULL THEN 'Visitor'
    ELSE 'User'
END AS user_type,
flag_converted,
CASE
    WHEN rating = 5 THEN '1'
    ELSE '0'
END AS is_best_rating,
COUNT(*) AS count
...

Since we’re adding COUNT() which is an aggregation, we need to modify the query with a GROUP BY. So all three other columns in SELECT except for count must be added to the GROUP BY:

SELECT 
CASE
    WHEN id_user IS NULL THEN 'Visitor'
    ELSE 'User'
END AS user_type,
flag_converted,
CASE
    WHEN rating = 5 THEN '1'
    ELSE '0'
END AS is_best_rating, COUNT(*) AS count 
from files
GROUP BY user_type, flag_converted, is_best_rating

And for this condition “The results must be sorted by count and is_best_rating in reverse order. And display only groups where count is greater than 1.”. You probably can do something like this:

SELECT 
CASE
    WHEN id_user IS NULL THEN 'Visitor'
    ELSE 'User'
END AS user_type,
flag_converted,
CASE
    WHEN rating = 5 THEN '1'
    ELSE '0'
END AS is_best_rating, COUNT(*) AS count 
from files
GROUP BY user_type, flag_converted, is_best_rating
HAVING count > 1
ORDER BY count DESC, is_best_rating DESC;

I think reverse order means the largest value first so ORDER BY xxx DESC is making sure of that. As for HAVING count > 1 its self-explanatory but with or without it, I don’t see any count=1; kind of make me think that I might’ve missed something.

Anyway here’s your updated fiddle