Solved

Most common products ordered

Posted on 2011-02-28
15
210 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
ID: 34997524
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
ID: 34997580
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
ID: 34997625
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:NewFourWalls
ID: 34997745
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
ID: 34998164
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
ID: 34998167
Sorry about that, like reb said, add the group by in there.
0
 

Author Comment

by:NewFourWalls
ID: 34998245
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
 
LVL 15

Expert Comment

by:derekkromm
ID: 34998264
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
ID: 34998302
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
ID: 34998329
Ignore my previous post, the join is necessary due to the Id field (Apologies!)
0
 

Author Comment

by:NewFourWalls
ID: 34999865
Hi Derek,

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

Accepted Solution

by:
derekkromm earned 350 total points
ID: 34999942
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
ID: 34999945
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
ID: 35000219
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
ID: 35002803
Thanks, hope the split is ok
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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