CODEWITHSUNDEEP
phpmysqlselect
Glooh
Glooh

Reputation: 521

How to get TOP 10 in MySQL with combined data rows?

I have a challenge I can't seem to handle.

+------+--------+-----------+-------+
|  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 amount of books the users own, no matter the genre and would like to ORDER them by who has the most.

In this example, you see that Mary has 20 books, Pete 5 and John has 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 into one SQL? Should I use CONCAT or TOP or something? I use MySQL & PHP.

Upvotes: 3

Views: 1361

Answers (3)

liquorvicar
liquorvicar

Reputation: 6106

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 then 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 might want to rethink your schema slightly. Duplicating info is against the principles of normalisation. You might want to add a new owners table:

  +-----------+-------------+
  | owner_id  |  owner_name |
  +-----------+-------------+
  |     1     |    John     |
  |     2     |    Pete     |
  |     3     |    Mary     |
  +-----------+-------------+

and then reference this by owner_id in your books table.

Upvotes: 6

Sandeep Manne
Sandeep Manne

Reputation: 6092

SELECT sum(books) as book_count, user  FROM `books` GROUP BY (user) order by book_count DESC

Upvotes: 1

juergen d
juergen d

Reputation: 204784

select user, sum(books) as total
from your_table
group by user
order by sum(books)
limit 10

Upvotes: 2

Related Questions