php mysql display by views

Question

hey,

thanks for loking.

i have 3 tables

books, book_category, book_click.

books table book_id, url, title, img, short_desc, featured, enable

book_category table book_id, category_id

book_clicks table site_id, time, views

i like to get data from books table by view SUMS but this only returns one result

SELECT  books.book_id, url, title, short_desc, img, featured, SUM(views) as total_views
 FROM  books,book_category,book_click  WHERE  enable=1 AND category_id=7
AND books.book_id=book_category.book_id
ORDER BY  strong texttotal_views DESC

Answer 1

Checkout the mysql WITH ROLLUP

UPDATE

After reading your quesion again it might be as simple as adding GROUP BY books.book_id

SELECT  books.book_id, url, title, short_desc, img, featured, SUM(views) as total_views
 FROM  books,book_category,book_click  WHERE  enable=1 AND category_id=7
AND books.book_id=book_category.book_id
ORDER BY  strong texttotal_views DESC GROUP BY books.book_id

Original Answer:

http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html

SELECT  books.book_id, url, title, short_desc, img, featured, SUM(views) as total_views
 FROM  books,book_category,book_click  WHERE  enable=1 AND category_id=7
AND books.book_id=book_category.book_id
ORDER BY  strong texttotal_views DESC GROUP BY your_grouping WITH ROLLUP;

Example

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |