How to get TOP 10 in MySQL with concatenated data rows?
I have a problem that I cannot deal with.
+------+--------+-----------+-------+
| id | user | genres | books |
+------+--------+-----------+-------+
| 1 | John | crimes | 2 |
| 2 | John | scienc | 1 |
| 3 | John | nature | 4 |
| 4 | Pete | nature | 3 |
| 5 | Pete | crime | 2 |
| 6 | Mary | nature | 20 |
+------+--------+-----------+-------+
I would like to have a SQL query that gets the total number of books a user owns, regardless of genre, and would like to ORDER them to whoever has the most.
In this example, you can see that Mary has 20 books, Pete 5 and John 7, so my desired result would be an array like:
result[0][user] = "Mary";
result[0][total] = 20;
result[1][user] = "John";
result[1][total] = 7;
result[2][user] = "Pete";
result[2][total] = 5;
How can I get this in one SQL? Should I use CONCAT or TOP or something else? I am using MySQL and PHP.
source to share
You need GROUP BY with SUM
SELECT `user`, SUM(books) AS total_books
FROM `table`
GROUP BY `user`
ORDER BY total_books DESC
If you only want the first 10, you can use
SELECT `user`, SUM(books) AS total_books
FROM `table`
GROUP BY `user`
ORDER BY total_books DESC LIMIT 10`
By the way, you can rethink your schema a bit. Duplication of information is contrary to the principles of normalization. You might want to add a new table owners
:
+-----------+-------------+
| owner_id | owner_name |
+-----------+-------------+
| 1 | John |
| 2 | Pete |
| 3 | Mary |
+-----------+-------------+
and then put it owner_id
in the table books
.
source to share