Using sum function against column c when column a is unique

1

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

image

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

2

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

3

[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.

4

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

5

Figured it out

LEFT JOIN
(SELECT
PT.[Order Number]
,SUM(PT.[Days to Deliver]) OVER(PARTITION BY PT.[Order Number]) AS [Combo days]
FROM #tmpPARTSTTD11 PT
GROUP BY
PT.[Order Number]
,PT.[Days to Deliver]
) PT2
ON PT2.[Order Number] = PT.[Order Number]

6

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