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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculating totals based on User ID

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
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-14 : 02:27:57
UserId FName LName PdtId OdrTl NoOfOrd OrderTotal OrderId

612 Elle Morgan 276 187.50000 1 187.50000 2193
613 Rebecca Gillard 64 47.50000 1 47.50000 2192
615 Cameron Guest 308 645.83000 1 645.83000 2196
616 Philip Medway 554 65.00000 2 130.00000 2197,2198
617 Lee Southwell 19 220.00000 1 220.00000 2200
618 Ryan Conway 174 200.00000 1 200.00000 2201

veeranjaneyulu
Go to Top of Page

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 posted

SELECT
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -