SUM and JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SUM and JOIN

Author

Topic

dannylister
Starting Member

9 Posts

Posted - 2008-09-07 : 15:32:32

Hi all,

I am a fairly novice user of SQL and have a problem regarding a SUM and JOIN statement. I have two tables:

'Product' table which for this example can contain an 'ID', 'Name' and 'Price' column.
'OrderMember' table which for this example can contain a 'ProductID' and 'Quantity' column.

What I want to do is return the top 3 products sold. So far I can successfully return the 'ProductID' and 'QuantityTotal' but I want to return the product details from the product table for each of the top 3 products. So far my SQL statement looks like this:

select 
	top 3 
	sum(OrderMember.Quantity) as QuantityTotal, 
	OrderMember.ProductId
from Product
join OrderMember on Product.Id = OrderMember.ProductId
group by OrderMember.ProductId
order by QuantityTotal desc

This statement would return:

QuantityTotal   ProductId
8	          4
5	          1
1	          6

Any help would be great.
Danny.

http://www.dannylister.co.uk/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-07 : 16:05:08

[code]SELECT t.ID,t.Name,t.Price,t.TotalQuantity
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY om.TotalQuantity DESC) AS RowNo,
p.ID,p.Name,p.Price,om.TotalQuantity
FROM Product p
INNER JOIN (SELECT ProductID,SUM(Quantity) AS TotalQuantity
FROM OrderMember
GROUP BY ProductID)om
ON om.ProductID =p.ID
)t
WHERE t.RowNo<=3[/code]

dannylister
Starting Member

9 Posts

Posted - 2008-09-07 : 16:18:52

Thank you for reply. It worked a treat :)

http://www.dannylister.co.uk/

dannylister
Starting Member

9 Posts

Posted - 2008-09-15 : 18:23:24

Moving on from this, I now have pretty much the same stored SQL statement:

select	
		t.Id,
		t.CategoryId,
		t.Name,
		t.Description,
		t.Price,
		t.ImageUrl,
		t.ThumbnailUrl,
		t.IsEnabled,
		t.DateCreated,
		t.DateModified,
		t.DateDisabled,
		t.ModifiedBy,
		t.TimeStamp
	from
		(select 
			row_number() over(order by om.TotalQuantity desc) as RowNo,
			p.Id,
			p.CategoryId,
			p.Name,
			p.Description,
			p.Price,
			p.ImageUrl,
			p.ThumbnailUrl,
			p.IsEnabled,
			p.DateCreated,
			p.DateModified,
			p.DateDisabled,
			p.ModifiedBy,
			p.TimeStamp
		 from 
			Product p
		 inner join 
			(select 
				ProductID,
				sum(Quantity) as TotalQuantity
			 from 
				OrderMember 
			 group by 
				ProductID) om
		 on 
			om.ProductID = p.ID
		 where
			p.ThumbnailUrl is not null and
			p.IsEnabled = 1
		) t
	where 
		t.RowNo <= @NumberOfBestSellers

What I wish to happen is that when the number of rows returned is actually less than the @NumberOfBestSellers value, I wish to add random products to make it up to the value stored in @NumberOfBestSellers. For example, if i want 5 products returned and only 2 come back from the above, I'd like another 3 random products (ones not already added) to be added to the data returned.

I know this may seem I bit tricky (it sure does to me) but any help would be great :)

Danny.

http://www.dannylister.co.uk/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-15 : 21:00:12

I needed to guess on what exactly you wanted (I'm assuming the only scenerio where there are less best sellers than @NumberOfBestSellers is when the OrderMember table only has a few products in it)?

if so you can try



select	
		t.Id,
		t.CategoryId,
		t.Name,
		t.Description,
		t.Price,
		t.ImageUrl,
		t.ThumbnailUrl,
		t.IsEnabled,
		t.DateCreated,
		t.DateModified,
		t.DateDisabled,
		t.ModifiedBy,
		t.TimeStamp
	from
		(select 
			row_number() over(order by om.TotalQuantity desc, newID()) as RowNo,
			p.Id,
			p.CategoryId,
			p.Name,
			p.Description,
			p.Price,
			p.ImageUrl,
			p.ThumbnailUrl,
			p.IsEnabled,
			p.DateCreated,
			p.DateModified,
			p.DateDisabled,
			p.ModifiedBy,
			p.TimeStamp
		 from 
			Product p
		 Left join 
			(select 
				ProductID,
				sum(isnull(Quantity,0)) as TotalQuantity
			 from 
				OrderMember 
			 group by 
				ProductID) om
		 on 
			om.ProductID = p.ID
		 where
			p.ThumbnailUrl is not null and
			p.IsEnabled = 1
		) t
	where 
		t.RowNo <= @NumberOfBestSellers

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources