select 
books.book_id, books.title, books.date_added, authors.name as `author`,
publisher.name as pubname, nested_category.name as catname, books.retail_price as `price`, books.`language`,
b.scount, b.samount,
books.offshelf
 from 
books left join (
	select book_id, sum(booksale.qty) as scount, sum(booksale.sale_amount) as samount 
	from booksale 
	where 
	booksale.sale_datetime >= '2013-07-01 00:00:00'
	and booksale.sale_datetime <= '2013-12-31 23:59:59'
	group by book_id
)
 as b on books.book_id = b.book_id
 left join publisher on books.publisher_id = publisher.publisher_id
 left join nested_category on books.category_id = nested_category.category_id
 left join authors on books.author_id = authors.author_id
where publisher.localpub = 'Y'
order by samount desc