Find Number of purchased item on this SQL query


Find Number of purchased item on this SQL query

Query:
--------
SELECT DISTINCT [Order], FirstName, LastName, OrderDate, PaymentType, Sum(OrderTotal)OVER(PARTITION BY [Order] ) OrderTotal, RTRIM(SUBSTRING(ISNULL((SELECT '|' + ProductName FROM temp t2 WHERE(t1.[Order] = t2.[Order])for xml path('')),' '),2,2000)) ProductName,RTRIM(SUBSTRING(ISNULL((SELECT '|' + ProductName FROM temp t2 WHERE(t1.[Order] = t2.[Order])for xml path('')),' '),2,2000)) ProductName, CouponCode,ShippingTotal,Weight, ShipType, ShipVia, orderstatus, transaction_id, authorizedmessage FROM temp t1


on this query, i want to find out number of purchase item.
Please give me suggestion how to find out from this query.
This query is perfectly work in my project just want to find out number of purchased item through this query.

Please help me out.

Thank you.
citadelindAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
I guess we have to keep playing with the Partition by, what about
SELECT DISTINCT 
	[Order], 
	FirstName, 
	LastName, 
	OrderDate, 
	PaymentType, 
	Sum(OrderTotal) OVER (PARTITION BY [Order] ) OrderTotal, 
	RTRIM(SUBSTRING(ISNULL((SELECT '|' + ProductName FROM temp t2 WHERE(t1.[Order] = t2.[Order])for xml path('')),' '),2,2000)) ProductsName,
	CouponCode,
	ShippingTotal,
	Weight, 
	ShipType, 
	ShipVia, 
	orderstatus, 
	transaction_id, 
	authorizedmessage,
	count([ProductName]) OVER (Partition by FirstName + LastName) items_purchased
FROM temp t1

Open in new window

0
 
ralmadaCommented:
maybe something like this?


SELECT DISTINCT 
	[Order], 
	FirstName, 
	LastName, 
	OrderDate, 
	PaymentType, 
	Sum(OrderTotal) OVER (PARTITION BY [Order] ) OrderTotal, 
	RTRIM(SUBSTRING(ISNULL((SELECT '|' + ProductName FROM temp t2 WHERE(t1.[Order] = t2.[Order])for xml path('')),' '),2,2000)) ProductsName,
	CouponCode,
	ShippingTotal,
	Weight, 
	ShipType, 
	ShipVia, 
	orderstatus, 
	transaction_id, 
	authorizedmessage,
	count([Order]) OVER (Partition by [ProductName]) items_purchased
FROM temp t1

Open in new window

0
 
RafaelVetroneCommented:
Does this query work at all? Isnt it lacking group by?
Anyway to count the number of rows of this query, you could store the contents of this query into a temporary table, and the query the temporary table with count.
The way it is, the count will only count inside each group.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
citadelindAuthor Commented:
no, this gives wrong result for item_purchased.

this query
-----------
SELECT DISTINCT
      [Order],
      FirstName,
      LastName,
      OrderDate,
      PaymentType,
      Sum(OrderTotal) OVER (PARTITION BY [Order] ) OrderTotal,
      RTRIM(SUBSTRING(ISNULL((SELECT '|' + ProductName FROM temp t2 WHERE(t1.[Order] = t2.[Order])for xml path('')),' '),2,2000)) ProductsName,
      CouponCode,
      ShippingTotal,
      Weight,
      ShipType,
      ShipVia,
      orderstatus,
      transaction_id,
      authorizedmessage,
      count([Order]) OVER (Partition by [ProductName]) items_purchased
FROM temp t1

- i want the number of products customer purchased
- Please give me this query.
0
 
ralmadaCommented:
>> i want the number of products customer purchased <<
Doesn't mean anything to me. Please be more specific as to where this information is stored.
Also, Please post some sample data and the expected result so that I understand your requirements


0
 
ralmadaCommented:
In the mean time try
SELECT DISTINCT 
	[Order], 
	FirstName, 
	LastName, 
	OrderDate, 
	PaymentType, 
	Sum(OrderTotal) OVER (PARTITION BY [Order] ) OrderTotal, 
	RTRIM(SUBSTRING(ISNULL((SELECT '|' + ProductName FROM temp t2 WHERE(t1.[Order] = t2.[Order])for xml path('')),' '),2,2000)) ProductsName,
	CouponCode,
	ShippingTotal,
	Weight, 
	ShipType, 
	ShipVia, 
	orderstatus, 
	transaction_id, 
	authorizedmessage,
	count([ProductName]) OVER (Partition by [Order]) items_purchased
FROM temp t1

Open in new window

0
 
citadelindAuthor Commented:
Attached Excel file, which temp table record.

How to find number of products  purchased by customer based on the orders?

Attached this table and fire about query and see the result.

And give me number of products purchased by customer.

Please give me solution
 Data.xls
0
 
citadelindAuthor Commented:
Excellent Solution.

Thank for help....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.