SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculating totals based on User ID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bullentoss
Starting Member

Thailand
1 Posts

Posted - 09/14/2013 :  00:08:59  Show Profile  Reply with Quote
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

India
164 Posts

Posted - 09/14/2013 :  01:36:22  Show Profile  Reply with Quote
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

Edited by - VeeranjaneyuluAnnapureddy on 09/14/2013 02:23:40
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
164 Posts

Posted - 09/14/2013 :  02:27:57  Show Profile  Reply with Quote
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

Edited by - VeeranjaneyuluAnnapureddy on 09/14/2013 02:38:19
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/15/2013 :  06:35:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000