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 |