Solved

Most common products ordered

Posted on 2011-02-28
15
209 Views
Last Modified: 2012-05-11
Hello Experts,

I have a database full of products which were ordered via our website. What i would like to do is run an SQL query which would show the top 10 products ordered between a given date range.

There is 2 sets of fields which need to match to determine they are the same.
RetailCode must = SupplierCode
RetailDescription must = SupplierDescription

I would like to show the user running the query the number of times it has been ordered.

If i have not explained myself very well please feel free to ask any questions which will assist you in providing a solution.

Thank you in advance.


David
0
Comment
Question by:NewFourWalls
  • 6
  • 5
  • 4
15 Comments
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
Is this all residing on 1 table?

A generic query would look something like. Not sure if thats enough to get you off and running?

select top 10 t.*
from
	table t
	inner join (
		select RetailCode, count(*) c from table
	) t1
		on	t.RetailCode = t1.RetailCode
order by
	t1.c desc

Open in new window

0
 

Author Comment

by:NewFourWalls
Comment Utility
Hi derekkromm,

Yes, the data is all on one table.

Unfortunately, the SQL you provided doesnt make a whole load of sense to me :( There is no mention of the retailcode matching the suppliercode fields. Sorry.

Thanks,


D.
0
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
does this help?

select top 10 t.*, t1.c
from
	table t
	inner join (
		select RetailCode, count(*) c from table where RetailCode = SupplierCode and RetailDescription = SupplierDescription
	) t1
		on	t.RetailCode = t1.RetailCode
order by
	t1.c desc

Open in new window


0
 

Author Comment

by:NewFourWalls
Comment Utility
Thank you for your input, unfortunately the SQL is producing an error.

Is there something i have done wrong?

Please see the screenshot.

Error
0
 
LVL 25

Expert Comment

by:reb73
Comment Utility
Your subquery (aliased as t1) is missing the group by clause, change it as follows -

 
SELECT RetailCode, COUNT(*) as C
FROM OrderProducts
WHERE RetailCode = SupplierCode and RetailDescription = SupplierDescription
GROUP BY RetailCode

Open in new window

0
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
Sorry about that, like reb said, add the group by in there.
0
 

Author Comment

by:NewFourWalls
Comment Utility
Thanks Guys, that does seem to work great, however, I also need to show other fields from the query but when i add them it gives me the group by error again.

The only fields i need to show / use are;

ID, RetailCode, RetailDescription, SupplierCode, SupplierDescription, C << The COUNT

Can you tell me if this is possible?

Thanks,


David
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
select top 10 t.ID, t.RetailCode, t.RetailDescription, t.SupplierCode, t.SupplierDescription, t1.c
from
	table t
	inner join (
		select RetailCode, count(*) c from table where RetailCode = SupplierCode and RetailDescription = SupplierDescription group by RetailCode
	) t1
		on	t.RetailCode = t1.RetailCode
order by
	t1.c desc

Open in new window

0
 
LVL 25

Expert Comment

by:reb73
Comment Utility
There shouldn't be a neccesity to do any joins if the requirement is as stated, the simple query below may suffice -
select	top 10 
	ID, RetailCode, RetailDescription, SupplierCode, SupplierDescription, count(*) as c
from
	OrderProducts 
where
	RetailCode = SupplierCode and RetailDescription = SupplierDescription
group by
	RetailCode
order by
	t1.c desc

Open in new window

0
 
LVL 25

Expert Comment

by:reb73
Comment Utility
Ignore my previous post, the join is necessary due to the Id field (Apologies!)
0
 

Author Comment

by:NewFourWalls
Comment Utility
Hi Derek,

Your query produced some weird results... please see the attachment. results
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 350 total points
Comment Utility
Hmm, ya, you won't be able to show the ID column since you'll have X rows, where X is the count.

Try this:

select top 10 RetailCode, RetailDescription, SupplierCode, SupplierDescription, count(*) as 'Count'
from OrderProducts
where RetailCode = SupplierCode and RetailDescription = SupplierDescription
group by RetailCode, RetailDescription, SupplierCode, SupplierDescription
order by count(*) desc
0
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 150 total points
Comment Utility
Perhaps you are looking for something like this?
select top 10 Max(Id) as Id, RetailCode, RetailDescription, SupplierCode, SupplierDescription, Count(Id) as c
from OrderProducts
where RetailCode = SupplierCode and RetailDescription = SupplierDescription
group by RetailCode
order by Count(Id) desc

Open in new window

0
 

Author Comment

by:NewFourWalls
Comment Utility
Thanks for all your help. I'm sorry for the confusion, i didnt need the ID column!

Dereks example worked perfect, code below.

Reb73 also contributed to the solution, are you's happy for a split in points? if so, what % do you both think is fair? Sorry to do this, but last thing i want to do is alienate two highly ranked experts and prevent you from helping me in the future.
SELECT     TOP (10) RetailCode, RetailDescription, SupplierCode, SupplierDescription, COUNT(*) AS 'Count'
FROM         OrderProducts
WHERE     (RetailCode = SupplierCode) AND (RetailDescription = SupplierDescription)
GROUP BY RetailCode, RetailDescription, SupplierCode, SupplierDescription
ORDER BY 'Count' DESC

Open in new window

0
 

Author Closing Comment

by:NewFourWalls
Comment Utility
Thanks, hope the split is ok
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now