asked on

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.

ralmada

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

RafaelVetrone

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.

citadelind

ASKER

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.

ralmada

>> 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

ralmada

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

citadelind

ASKER

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

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

citadelind

ASKER

Excellent Solution.

Thank for help....