MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
  	sort INT
);

INSERT INTO users (username, email, sort) VALUES ('user1', 'user1@example.com', 50);
INSERT INTO users (username, email, sort) VALUES ('user2', 'user2@example.com', 30);
INSERT INTO users (username, email, sort) VALUES ('user3', 'user3@example.com', 20);
INSERT INTO users (username, email, sort) VALUES ('user4', 'user4@example.com', 90);
INSERT INTO users (username, email, sort) VALUES ('user5', 'user5@example.com', 40);
INSERT INTO users (username, email, sort) VALUES ('user6', 'user6@example.com', 70);
SELECT
	sort,
    username,
    GROUP_CONCAT(email) AS email_concat,
    COUNT(*) OVER (ORDER BY NULL) AS total_count
FROM users
GROUP BY id
ORDER BY sort;


sort
username
email_concat
total_count


20
user3
user3@example.com
6

30
user2
user2@example.com
6

40
user5
user5@example.com
6

50
user1
user1@example.com
6

70
user6
user6@example.com
6

90
user4
user4@example.com
6


SELECT
	sort,
    username,
    GROUP_CONCAT(email) AS email_concat,
    COUNT(*) OVER (ORDER BY sort) AS total_count
FROM users
GROUP BY id
ORDER BY sort;


sort
username
email_concat
total_count


20
user3
user3@example.com
1

30
user2
user2@example.com
2

40
user5
user5@example.com
3

50
user1
user1@example.com
4

70
user6
user6@example.com
5

90
user4
user4@example.com
6

sort	username	email_concat	total_count
20	user3	user3@example.com	6
30	user2	user2@example.com	6
40	user5	user5@example.com	6
50	user1	user1@example.com	6
70	user6	user6@example.com	6
90	user4	user4@example.com	6