[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Find Number of purchased item on this SQL query

Posted on 2011-10-14
8
Medium Priority
?
183 Views
Last Modified: 2012-05-12

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.
0
Comment
Question by:citadelind
  • 4
  • 3
8 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 36968128
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
 
LVL 2

Expert Comment

by:RafaelVetrone
ID: 36968251
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
 

Author Comment

by:citadelind
ID: 36968740
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 41

Expert Comment

by:ralmada
ID: 36968879
>> 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
 
LVL 41

Expert Comment

by:ralmada
ID: 36968919
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
 

Author Comment

by:citadelind
ID: 36968960
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
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 36969033
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
 

Author Closing Comment

by:citadelind
ID: 36972344
Excellent Solution.

Thank for help....
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question