about calculating the top ten

[dummyagain]

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

[Krzemo]

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

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

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.

[dummyagain]

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

[Krzemo]

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:

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.