Solved

Most common products ordered

Posted on 2011-02-28
15
211 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
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)

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 30
ASP exit 10 20
SQL Availablity Groups List 2 8
href return to normal window 5 13
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

828 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