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.
Author |
Topic |
bullentoss
Starting Member
1 Post |
Posted - 2013-09-14 : 00:08:59
|
Hi All,I have created an SQL report that returns order data from a shop DB.How can you get the output to total the number of order instances and the total value of those orders, both per user_id? Is this possible?Cheers,Rob. |
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-14 : 01:36:22
|
Hi, See The OutPut;SELECT DISTINCT p.UserID , p.FirstName , p.LastName , p.ProductId , p.OrderTotal , COUNT(p.UserId) AS 'NumberOfOrders' , SUM(p.OrderTotal) AS 'UserOrderTotal' , (SELECT STUFF((SELECT ','+(OrderID) AS [text()] FROM (SELECT DISTINCT OrderId= cast(OrderId AS VARCHAR) FROM Products AS ps WHERE p.UserId = ps.UserId)x ORDER BY OrderId FOR XML PATH('')),1,1,'')) AS 'OrderId' FROM Products AS p GROUP By p.UserID,p.FirstName,p.LastName,p.ProductId,p.OrderTotal |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-14 : 02:27:57
|
UserId FName LName PdtId OdrTl NoOfOrd OrderTotal OrderId612 Elle Morgan 276 187.50000 1 187.50000 2193613 Rebecca Gillard 64 47.50000 1 47.50000 2192615 Cameron Guest 308 645.83000 1 645.83000 2196616 Philip Medway 554 65.00000 2 130.00000 2197,2198617 Lee Southwell 19 220.00000 1 220.00000 2200618 Ryan Conway 174 200.00000 1 200.00000 2201veeranjaneyulu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-15 : 06:35:16
|
quote: Originally posted by bullentoss Hi All,I have created an SQL report that returns order data from a shop DB.How can you get the output to total the number of order instances and the total value of those orders, both per user_id? Is this possible?Cheers,Rob.
i think it should be this as per sample output postedSELECT p.OrderID, p.UserID , p.FirstName, p.LastName, p.ProductId, p.OrderTotal, COUNT(p.OrderID) OVER (PARTITION BY p.UserID) AS 'NumberOfOrders', SUM(p.OrderTotal) OVER (PARTITION BY p.UserID) AS 'UserOrderTotal'FROM Products AS p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|