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