help with an SQL problem - 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 2000 Forums

SQL Server Development (2000)

help with an SQL problem

Author

Topic

noclist
Starting Member

4 Posts

Posted - 2007-10-28 : 14:30:00

Hi, I posted this in another forum as well but I'm not sure it was the right venue, it seems like it might make more sense in here.
Anyway...

For a class, I need to write SQL statements for an assortment of problems and I'm stuck on the last few:

What is the most popular combination of 2 products that are ordered simultaneously? The most popular combination is defined as follows: Consider any combination of two products such as (LS200A, ATM50A). Looking at all orders, count the number of times these two products were present in an order. An order could of course have other products in it than just these two, so you are not limited to examining only orders with exactly two products. If you repeat this process for all possible combinations of two products, then that combination which was present the highest number of times in all orders is the most popular combination of two products that were ordered simultaneously. The quantities ordered are not relevant in this context.

The result of the query should look something like the following:

First Product ----- Second Product ------ Max Number of Times
--- LS200A --------- ATM50A ------------------- 7 -----------

(Using Access)
I know little to no SQL, just the basics of it and my professor is pretty terrible at teaching it. I'm thinking I'll need to make a virtual table with a CREATE VIEW but I'm not sure where to go after that. I also need to write statements for the most popular combination of three products. If anyone could give me a hand, I would really appreciate it. Thanks a lot.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 14:33:15

What have you tried so far? You didn't get any replies because

A) this is free help, and people are pretty busy sometimes
B) your question is general, yet you want a specific answer
C) no one will do your homework for you

Access uses SQL (sort of) which can give you a sense of the general syntax you need to write a SQL statement, since SQL stands for "Structured Query Language" the rules are fairly consistent

We can't help you write the query, because you only posted the desired result, not the structure of the source data (or a sample) to derive those results from.

noclist
Starting Member

4 Posts

Posted - 2007-10-28 : 14:42:03

Well I'm only looking for the general way to code the problem to find the specific answer, there is no actual data. Basically, I'm confused as how to use the WHERE statement to rank the products by popularity. I guess I should have worded the fact that I need Access help as opposed to SQL help since it only uses a variation of it. Thanks for the info though.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 15:07:28

Well, if you use Access to create a query in design view, you can actually look at the SQL behind that. In design view, there is a little green triangle, if you click that a drop down will appear which will let you pick a box that as the letters SQL in it. Click that and you can see how it generally looks.

The criteria you put in Access design view is the "Where" portion

Generally it would look like this

Select [First Product], [Second Product], max(Number) as MaxNumber
FROM ProductTable
Group by [First Product],[Second Product]
Order by max(number) desc

to product the max(number) grouped by the first and second product columns ordered by the highest max(number) down.

To filter using WHERE:
Select [First Product], [Second Product], max(Number) as MaxNumber
FROM ProductTable
WHERE [First Product] = 'ABCDEF'
Group by [First Product],[Second Product]
Order by max(number) desc

noclist
Starting Member

4 Posts

Posted - 2007-10-28 : 16:19:38

Hm, well I should have mentioned my schema restricts me to a product table with ProductID, ProductName, ProductFamily, and Price. So I can't figure out how to seperately count combinations of 2 products using just one ProductID field.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 16:54:26

You would have to use derived tables and joins

Not sure I understand your problem completely, but assuming one ProductID can have multiple ProductNames...


Select a.ProductID,a.ProductName as MainProductName,b.ProductName as SecondProductName,Count(a.ProductID)
From [ProductTable] a inner join (Select ProductID,ProductName From [ProductTable]) b
on a.[ProductID] = b.[ProductID]
where a.[ProductName] <> b.[ProductName]
Group by a.ProductID,a.ProductName,b.ProductName
Order by Count(a.ProductID) desc

Assumes that ProductID is not a primary key, as if the product ID were the PK, this would have no chance of working.

If productID is the PK, you might have some issues

Which would produce count, and list the ProductNames next to eachother..

Untested, but just so you can get the idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources