SQLTeam.com | Weblogs | Forums

Using sum function against column c when column a is unique


Hi I am creating a table like the below in SQL but am struggling with producing the last column


I have tried using distinct & partitions but not cracked it yet...
,SUM(PT.[Days to Deliver]) over(partition by PT.[Order Number]) **(invalid for sum operator)
,SUM(distinct(PT.[Order Number]) PT.[Days to Deliver] - just getting in a muddle so some help would be fab

Thank you


Just thinking.. Do I need to create a left join group by statement first?


[Sum of DTD] = SUM([days to deliver]) OVER(PARTITION BY [Order number])

but you can't use that in a create table statement if that is what you are trying to do.


Hi, I am trying to use it within a create table statement.


Figured it out

PT.[Order Number]
,SUM(PT.[Days to Deliver]) OVER(PARTITION BY PT.[Order Number]) AS [Combo days]
PT.[Order Number]
,PT.[Days to Deliver]
) PT2
ON PT2.[Order Number] = PT.[Order Number]


drop table orders
create table orders
OrderNumber int,
PartNumber bigint,
DaysToDeliver int


insert into orders values(1,4456,7)
insert into orders values(1,8455,8)
insert into orders values(2,10778,6)
insert into orders values(3,8801,5)
insert into orders values(3,2613,12)
insert into orders values(3,247,35)
insert into orders values(4,6944,4)
insert into orders values(4,4553,9)

select a.OrderNumber,PartNumber,DaysToDeliver,b.SumOfDTD from orders a
inner join
(select OrderNumber,sum(DaysToDeliver) as SumOfDTD from orders
group by OrderNumber) b
on a.OrderNumber = b.OrderNumber