Getting Items that were ordered alone

Hello Experts. You may have more luck at this than me.

I am interested in finding the quantity of items that were ordered alone. I have an orderid field and a product field. So the count of the orderid has to equal one and the have them grouped by product.

Example of how data looks like

I am looking for transactions like orderid 3 and 5.

OrderID Product 1 hotdog 1 burger 1 taco 2 burrito 2 snack 2 chips 3 burger 4 hotdog 4 burger 4 taco 5 burrito 6 snack 6 chips

When i run

SELECT product,count(orderid)

From Table

Where BusinessDateID = 20060725

groupby product

having(count(orderid)=1)

I only get back items that were only sold once.

I am looking for a result that looks like this

Product Ordered alone hotdog 2 burger 3 taco 4 burrito 32 snack 12 chips 76

This seems to provide your desired results:

Code Snippet

SET NOCOUNT ON

DECLARE @.Orders table
( RowID int IDENTITY,
OrderID int,
Product varchar(20)
)

INSERT INTO @.Orders VALUES ( 1, 'hotdog' )
INSERT INTO @.Orders VALUES ( 1, 'burger' )
INSERT INTO @.Orders VALUES ( 1, 'taco' )
INSERT INTO @.Orders VALUES ( 2, 'burrito' )
INSERT INTO @.Orders VALUES ( 2, 'snack' )
INSERT INTO @.Orders VALUES ( 2, 'chips' )
INSERT INTO @.Orders VALUES ( 3, 'burger' )
INSERT INTO @.Orders VALUES ( 7, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'hotdog' )
INSERT INTO @.Orders VALUES ( 4, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'taco' )
INSERT INTO @.Orders VALUES ( 5, 'burrito' )
INSERT INTO @.Orders VALUES ( 6, 'snack' )
INSERT INTO @.Orders VALUES ( 6, 'chips' )

SELECT
Product,
Count = sum( ProdCount )
FROM (SELECT
Product = max( Product ),
ProdCount = count( Product )
FROM @.Orders
GROUP BY OrderID
HAVING ( count( OrderID ) = 1 )
) dt
GROUP BY Product

Product Count
-- --
burger 2
burrito 1

That returns the total of OrderID's 3, 5, 7 -all singleton purchases.

|||

Thank you for the quick response.

I am having trouble executing the query above. Is there away to get around inserting all those entries?

Right now i can get all of the Orderid's that had only one product. I can't figure out how to attach the product next to the orderid. To get the orderid's with only one product i did

SELECT orderid

From dbo.vFactOrderitemDetail

Where BusinessDateID = 20060724

GROUPBY orderid

having(count(orderid)=1)

I get a list that looks like this.

6072401121160951
6072473174424676
6072435021178729
6072409154427790
6072408391169292
6072483063312181
6072471601109909
6072437871152147
6072490014417771

I want to have the product next to the orderid on this list how would i do that?

|||

Davy579124 wrote:

I am having trouble executing the query above.

What kind of trouble are you having? (I think that the code will run exactly as it is -all you have to do is 'cut and paste' into a query window.)

Davy579124 wrote:

Is there away to get around inserting all those entries?

All those 'entries' is a table of sample data -since you didn't provide any to work with.

Davy579124 wrote:

Right now i can get all of the Orderid's that had only one product. I can't figure out how to attach the product next to the orderid.

The best way is to examine the sample query, with the sample data that I posted.

Then when you understand what the query does, substitute your table name and your column names in the query.

You asked how to get a list of singleton Orders/Products, and the number of times ordered.

That is the solution I provided -which answered the question you posted.

Now like you have 'revised' the requirements.

That's ok, but we can't help you get something you don't tell us about.

So, looking at the query I posted, you will see that there is a sub-query.

The sub-query can be altered to return just the OrderID (instead of Product, and Count).

Then the 'outer' query can JOIN the @.Orders table with the sub-query (as a derived table) and return the Product for that OrderID.

Somewhat like this: (using the same sample data...)

Code Snippet

DECLARE @.Orders table
( RowID int IDENTITY,
OrderID int,
Product varchar(20)
)

INSERT INTO @.Orders VALUES ( 1, 'hotdog' )
INSERT INTO @.Orders VALUES ( 1, 'burger' )
INSERT INTO @.Orders VALUES ( 1, 'taco' )
INSERT INTO @.Orders VALUES ( 2, 'burrito' )
INSERT INTO @.Orders VALUES ( 2, 'snack' )
INSERT INTO @.Orders VALUES ( 2, 'chips' )
INSERT INTO @.Orders VALUES ( 3, 'burger' )
INSERT INTO @.Orders VALUES ( 7, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'hotdog' )
INSERT INTO @.Orders VALUES ( 4, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'taco' )
INSERT INTO @.Orders VALUES ( 5, 'burrito' )
INSERT INTO @.Orders VALUES ( 6, 'snack' )
INSERT INTO @.Orders VALUES ( 6, 'chips' )

SELECT
o.OrderID,
o.Product
FROM @.Orders o
JOIN (SELECT OrderID
FROM @.Orders
GROUP BY OrderID
HAVING ( count( OrderID ) = 1 )
) dt
ON o.OrderID = dt.OrderID
ORDER BY o.OrderID

OrderID Product
-- --
3 burger
5 burrito
7 burger

|||

Many Thanks!

So instead of

INSERT INTO @.Orders VALUES ( 1, 'hotdog' )
INSERT INTO @.Orders VALUES ( 1, 'burger' )
INSERT INTO @.Orders VALUES ( 1, 'taco' )
INSERT INTO @.Orders VALUES ( 2, 'burrito' )
INSERT INTO @.Orders VALUES ( 2, 'snack' )
INSERT INTO @.Orders VALUES ( 2, 'chips' )
INSERT INTO @.Orders VALUES ( 3, 'burger' )
INSERT INTO @.Orders VALUES ( 7, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'hotdog' )
INSERT INTO @.Orders VALUES ( 4, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'taco' )
INSERT INTO @.Orders VALUES ( 5, 'burrito' )
INSERT INTO @.Orders VALUES ( 6, 'snack' )
INSERT INTO @.Orders VALUES ( 6, 'chips' )

I can replace it with a table? I have 2 tables for orderid and product.(dbo.FactOrderItemDetail and dbo.DimProductHierarchy)

Can i use a view? "vFactOrderitemDetail"

Thanks

Davy

|||Yes, you can use your own tables, and you may be able to use a view as well.|||I got it to work! Thanks for your help