Complicated Query? - 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

General SQL Server Forums

New to SQL Server Programming

Complicated Query?

Author

Topic

matt.orme
Starting Member

23 Posts

Posted - 2008-11-24 : 16:37:35

I have a simple table that I need to run what seems to me to be a complicated query. Here's an example of the data:

Table Name: History

- Ticket (Text)- / - SKU (Text)-

212624 / 10891780
212624 / 10947210
212624 / 11019510
212624 / 10231260
212628 / 013770
212628 / 151200
212628 / 11019590
212628 / 11006830
212628 / 11006970
212632 / 10074980
212632 / 10268530
212632 / 10268540
212634 / 10952040
212636 / 12288
212636 / 61805810
212636 / 10483840
212636 / 10223150
212636 / 10843574
212636 / 11051290
212636 / 10313260
212636 / 10952744
212636 / 10359700

Ticket here represents a single transaction with a single customer, while SKU represents the items they purchased. I need to determine which items were purchased most frequently with each SKU - perhaps the top 10. Ideally, this would populate another table with the "Main" SKU acting as a unique identifier for the row and 10 subsequent fields with one SKU per container in descending order of occurrence from columns 2 through 11.

Doing this for a single SKU is easy, but getting everything squared away for each unique SKU stumps me. It seems like a For Each loop of some kind, but I am not good with any of that...

Any help would be appreciated. Please let me know what vital info I left out, as I always seem to forget something.

Thank you

*EDIT* - Don't know if this matters, but a SKU only appears once on any given Ticket.

matt.orme
Starting Member

23 Posts

Posted - 2008-11-24 : 16:49:15

I think I posted this in the wrong section. Sorry!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 23:48:09

is this you want?


SELECT COUNT(Ticket) AS TicketCount,SKU
FROM Table
GROUP BY SKU

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-24 : 23:54:57

How are the SKUs related to each other - you say you want a main SKU and then others but you don't give any way to relate them, or do you just want a single list of the top 10 selling SKUs in descending order? If so then this will do that:

SELECT TOP 10 SKU, count(*) AS SKUSales
FROM History
GROUP BY SKU
ORDER BY count(*) DESC

matt.orme
Starting Member

23 Posts

Posted - 2008-11-25 : 11:32:19

Let me try to phrase this another way, as I am terrible at explaining these problems -

A given SKU has been sold on a number of tickets. On each ticket, a number of other SKUs have been sold. There are no duplicated SKUs on a given ticket. For a each unique SKU, what ten other SKUs occur most frequently on the same ticket?

This is as good as I was able to do. It only works for one SKU (I call it Given in this example)

SELECT Ticket INTO Temp
FROM History
WHERE SKU = Given;

This gives each individual ticket in a separate table where the given SKU was sold.

SELECT History.SKU INTO Temp2
FROM History, Temp
WHERE History.Ticket = Temp.Ticket AND History.SKU <> Given;

This gives all the SKUs sold on the same ticket as Given

SELECT TOP 10 SKU, Count(SKU) AS Count
FROM Temp2
GROUP BY SKU
ORDER BY Count Desc;

Hopefully this clears up my first post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:37:27

i didnt under stand purpose of last statement


SELECT TOP 10 SKU, Count(SKU) AS Count
FROM Temp2
GROUP BY SKU
ORDER BY Count Desc;

this will always give count as 1 as you're grouping by SKU and taking its own count

matt.orme
Starting Member

23 Posts

Posted - 2008-11-25 : 12:52:41

I was under the impression that when I grouped by SKU it combined the count? Am I wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 13:04:37

quote:
Originally posted by matt.orme

I was under the impression that when I grouped by SKU it combined the count? Am I wrong?

yes
it will return 1 for all count
grouping by SKU will make it 1 record per SKU

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-25 : 22:49:40

OK, I think I understand now - the example data you gave is misleading because no SKUs are repeated across the tickets.

Here's an example of how you can do what you want. I changed the SKUs so that it will actually give results that are meaningful. This is going to return all related SKUs, not just the top 10. You could work with this to get just related SKUs for one given SKU or top 10 etc.

DECLARE @History TABLE (Ticket int, SKU varchar(30))
INSERT @History (Ticket, SKU)
SELECT 212624, '1' UNION ALL
SELECT 212624, '2' UNION ALL
SELECT 212624, '3' UNION ALL
SELECT 212624, '4' UNION ALL
SELECT 212628, '2' UNION ALL
SELECT 212628, '3' UNION ALL
SELECT 212628, '4' UNION ALL
SELECT 212628, '5' UNION ALL
SELECT 212628, '6' UNION ALL
SELECT 212632, '1' UNION ALL
SELECT 212632, '2' UNION ALL
SELECT 212632, '3' UNION ALL
SELECT 212634, '6' UNION ALL
SELECT 212636, '1' UNION ALL
SELECT 212636, '3' UNION ALL
SELECT 212636, '4' UNION ALL
SELECT 212636, '5' UNION ALL
SELECT 212636, '6' UNION ALL
SELECT 212639, '3' UNION ALL
SELECT 212639, '4' UNION ALL
SELECT 212639, '5' UNION ALL
SELECT 212639, '6'

SELECT Main.SKU, Other.SKU, count(*)
FROM @History Main
INNER JOIN @History Other ON Other.Ticket = Main.Ticket AND Other.SKU <> Main.SKU
GROUP BY Main.SKU, Other.SKU
ORDER BY Main.SKU, count(*) DESC, Other.SKU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources