Adding rows changes the sum result with group by

Question

How do I add more columns without changing the result?

Currently this script gets the top 300 purchase order results by order value using a sum & grouping by the order items oi.nettprice This shows the correct results as shown below.

Results shown here

select top 300

oi.orderid, o.traderid, o.orderdate, 

SUM(oi.nettprice) AS nettprice

from orderitems AS oi

INNER JOIN orders AS o ON o.tradertype = 'S' AND o.id = oi.orderid 

where oi.ordertype = 'PO'
AND oi.status != 'CANCELLED'

group by
oi.orderid, o.traderid, o.orderdate

order by
nettprice DESC

Adding any additional columns to show more information changes the results and alters the sum of oi.nettprice as shown below, I've tried to use group by rollup but this gives the value in a separate row with the other column values as NULL.

select top 300

oi.orderid, o.traderid,
o.orderdate, oi.partid, oi.description, oi.partrevisionid, 

SUM(oi.nettprice) AS nettprice

from orderitems AS oi

INNER JOIN orders AS o ON o.tradertype = 'S' AND o.id = oi.orderid

group by rollup 
(oi.orderid, o.traderid, o.orderdate, oi.ordertype, oi.status, oi.partid, oi.description, 
oi.partrevisionid)

HAVING oi.ordertype ='PO'
AND oi.status != 'CANCELLED'

order by
nettprice DESC

Results using Group by rollup

How can I clear this up to include columns from either table with the correct total (SUM) for each purchase order without affecting the results?

Answer 1

Use sum() partition by to get the desired result

SELECT top 300 oi.orderid,
       o.traderid,
       o.orderdate,
       oi.partid,
       oi.description,
       oi.partrevisionid,
       SUM(oi.nettprice) OVER(PARTITION BY oi.orderid, o.traderid, o.orderdate)  AS nettprice
FROM orderitems AS oi
INNER JOIN orders AS o ON o.tradertype = 'S'
AND o.id = oi.orderid
WHERE oi.ordertype = 'PO'
AND oi.status != 'CANCELLED'
ORDER BY nettprice DESC