SQL one to many join table issue

Question

I have 2 tables which is one to many relationship. I want to join both table but the result shown after join is not what I need. Below is the table and sample data.

Create table #temp (
    DONo varchar(10),
    Amount decimal(18,2),
    OrderNo varchar(10)
)

Create table #order (
    Item varchar(10),
    Amount decimal(18,2),
    OrderNo varchar(10),
    PackingDate date
)

Insert into #temp(DONo, Amount, OrderNo)
VALUES('A123', 1000, 'O123')
Insert into #temp(DONo, Amount, OrderNo)
VALUES('A124', 1100, 'O124')

Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES('O123', 'Pen', 100, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Color', 800, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Box', 100, '2024-02-02')

I have try below join query

select 
   do.DONo, do. Amount, do.OrderNo, SUM(so.Amount)[[OrderAmount], so.PackingDate 
from #temp do
left join
  #order so
on
  do.OrderNo = so.OrderNo
group by
  do.DONo, do. Amount, do.OrderNo, so.PackingDate

Here is the output for above query

DONo    DOAmount  OrderNo   Amount     PackingDate
A123    1000.00   O123     900.00     2024-02-01
A123    1000.00   O123     100.00     2024-02-02
A124    1100.00   O124      NULL      NULL

The expected output I wish as below

DONo    DOAmount  OrderNo   Amount     PackingDate
A123    1000.00   O123     900.00     2024-02-01
A123              O123     100.00     2024-02-02
A124    1100.00   O124      NULL      NULL

Since the DO no is same for order O123, the amount for DO I need shown once. The reason why I need show the DO amount once is because when in the visualization, the tools will sum up the DO amount and it will become double up in the bar chart.

I think my direction is wrong and my table design was wrong. Please guide me how I can fixed it and show the result I need.

Answer 1

just replace

do.Amount,

by this

 case when row_number() over (partition by do.DONo order by do.DONo) = 1 then do.Amount else null end as Amount,

full query

select do.DONo, 
       case when row_number() over (partition by do.DONo order by do.DONo) = 1 then do.Amount else null end as Amount,
       do.OrderNo, 
       sum(so.Amount)OrderAmount, 
       so.PackingDate 
from temp do
  left join orders so on do.OrderNo = so.OrderNo
group by
  do.DONo, do. Amount, do.OrderNo, so.PackingDate

result

DONo	Amount	OrderNo	OrderAmount	PackingDate
A123	1000.00	O123	900.00	2024-02-01
A123	null	O123	100.00	2024-02-02
A124	1100.00	O124	null	null

See this demo