November 15, 2006 at 9:53 am
i have a products table. i have a order_Lines table. order lines is linked to products via prod_ID. I want to get the 30 best selling items. how do i total up all sales of each product and output it along with prod name?
November 15, 2006 at 9:56 am
sorted it, was a silly question. sorry for taking up your time.
November 16, 2006 at 7:48 am
Can you post the final solution so that users finding this thread with a search engine can benefit form your eureka moment?
November 16, 2006 at 8:07 am
sorry, here it is:
select
top 5 f.film_ID,f.film_Title, sum(ol.quantity)as'quantity'
from
film f inner join order_line ol on f.film_id = ol.film_id
where
f.film_id = ol.film_ID
group
by f.film_Title, f.Film_ID
order
by quantity desc
November 16, 2006 at 9:48 am
Don't you actually want to sort by the sum of the quantity though? I think your current query would only give you rows where the individual orders for the product had a high quantity. So if you had product A that was ordered once for a quantity of 75, that would appear above product B that had been ordered 700 times at a quantity of 1.
Depends on what you want...
Rick Todd
November 16, 2006 at 10:32 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply