March 16, 2011 at 12:49 am
How can I include in a query a single value and a total value.
I have order table, OrderDate, CustomerID, OrderAmount
There are multiple orders for same customerID and on mulitple dates. I would like to add 2 more columns that will be report total by date by customer and then total by customer. See a sample output below. Sorry I am new at sql and this query has me completely stumped
OrderDate CustomerID OrderAmount TotalbyDate TotalbyCustomer
1/1/2000 1 $10 $35 $60
1/1/2000 1 $25 $35 $60
2/1/2001 1 $10 $25 $60
2/1/2001 1 $15 $25 $60
March 16, 2011 at 1:05 am
How about this?
declare @table table
( OrderDate datetime ,
CustomerID int,
OrderAmount int
)
insert into @table
select '1/1/2000', 1, 10
union all select '1/1/2000' ,1, 25
union all select '2/1/2001' ,1, 10
union all select '2/1/2001' ,1, 15
select orderdate, CustomerID, orderamount ,
TotalDBydate = SUM(OrderAmount) OVER (PARTITION BY orderdate),
TotalByCustomer = SUM(OrderAmount) over (partition by customerid)
from @table
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply