about calculating the top ten

i get a transaction table with sub_quantity, bookid, and a book table with bookid...etc

i want to get the top ten books by adding up the sub_quantity of each book.
say there are 3 transactions
1 ASP 10 (quantity)
2 PHP 10
3 ASP 10
4 MYSQL 4
5 PHP 6

i write to show the ranking of the sold book.....by writing

SELECT b.name from book as b, transaction as t order by t.sub_quantity desc limit 10;

the query gets a problem of not adding up the price to rank...and so the result is the one i want....

can anyone tell me how to edit this query?

thanks

I don't know yours RDBMS name but I think that U have cartesian product.:)

IMHO there is missing "WHERE" clause.
For example:

Code:

---

SELECT b.name
 FROM book as b, transaction as t
 WHERE t.BookID=b.BookID
 order by t.sub_quantity desc limit 10;

---

Best regards,
Krzemo.

sorry, there are some typos for the query...there should be 'where' in my query.

other than that,
i would like to know how to sum up the book

in your query, it seems that it has not added up the total quantity of each book sold...what i want is to add up the quantity of each book sold and give the top ten best selling books....


anyway, thanks for your help and please correct me if i get some mistakes.....


thanks

Quote:

---

in your query, it seems that it has not added up the total quantity of each book sold...what i want is to add up the quantity of each book sold and give the top ten best selling books....

---

I still don't know yours RDBMS name but in SQL Server it lloks like:

Code:

---


SELECT TOP 10 * FROM
(
  SELECT b.name,SUM(t.sub_quantity) total_quantity
  FROM book as b, transaction as t
  WHERE t.BookID=b.BookID
  GROUP BY b.name
) tb
 order by tb.total_quantity DESC

---

Hope it helps.