Solved

Most common products ordered

Posted on 2011-02-28
15
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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