SQL Server add all rows where a condition is validate

I have a SQL Server database in which I need to add all the cost for a job family.

I have a table like this

Table : work

+-------+-----------+-----------+---------+
| wonum | cost1 | cost2 | wogroup |
+-------+-----------+-----------+---------+
| 1 | 30.12 | 157.14 | 1 |
| 2 | 110.10 | 0.00 | 1 |
| 3 | 12.67 | 45.45 | 1 |
| 4 | 0.00 | 0.00 | 4 |
| 5 | 400.00 | 11.54 | 4 |
+-------+-----------+-----------+---------+


I need to add cost1 and cost2 for all the row who have the same wogroup but only for the on where wonum = wogroup.

wogroup

where wonum = wogroup

Like this

+-------+-----------+-----------+---------+---------+
| wonum | cost1 | cost2 | wogroup | total |
+-------+-----------+-----------+---------+---------+
| 1 | 30.12 | 157.14 | 1 | 355.48 |
| 2 | 110.10 | 0.00 | 1 | null |
| 3 | 12.67 | 45.45 | 1 | null |
| 4 | 0.00 | 0.00 | 4 | 411.54 |
| 5 | 400.00 | 11.54 | 4 | null |
+-------+-----------+-----------+---------+---------+


In a perfect world, the null value would be the sum of cost1 and cost2 for the row but I'm not sure if it is possible...

EDIT: I can only do a select, it is for a BiRT report

1 Answer
1

Since this can change with more wonum being added, I'd have this as a VIEW

VIEW

declare @work table (wonum int , cost1 decimal (6,3), cost2 decimal (6,3) , wogroup int)
insert into @work
values
(1,30.12,157.14,1),
(2,110.10,0.00,1),
(3,12.67,45.45,1),
(4,0.00,0.00,4),
(5,400.00,11.54,4)


select
*,
total = case when wonum = min(wonum) over (partition by wogroup) then sum(cost1) over (partition by wogroup) + sum(cost2) over (partition by wogroup) end
from @work


RETURNS

+-------+-----------+-----------+---------+---------+
| wonum | cost1 | cost2 | wogroup | total |
+-------+-----------+-----------+---------+---------+
| 1 | 30.12 | 157.14 | 1 | 355.48 |
| 2 | 110.10 | 0.00 | 1 | null |
| 3 | 12.67 | 45.45 | 1 | null |
| 4 | 0.00 | 0.00 | 4 | 411.54 |
| 5 | 400.00 | 11.54 | 4 | null |
+-------+-----------+-----------+---------+---------+


YOUR QUERY

select
*,
total = case when wonum = min(wonum) over (partition by wogroup)
then sum(cost1) over (partition by wogroup) + sum(cost2) over (partition by wogroup)
else null
end
from work


Sorry I forgot to mention, I can only do a Select (so I can't make a declare) it is for a report...
– Charles
Jun 29 at 18:52

That doesn't make any sense, but here the declare is just for the test data... you wouldn't use it. You would just use the select and replace @work with your table name, which is work
– scsimon
Jun 29 at 18:53

select

@work

work

@Charles That should be fine. scsimon just declared a table variable named @work to simulate your table without messing around in a real database.
– FrankPl
Jun 29 at 18:54

@work

@scsimon I think you easily could add an else clause to fulfill the "In a perfect world" requirement.
– FrankPl
Jun 29 at 18:55

else

Fair suggestion @FrankPl, edited
– scsimon
Jun 29 at 18:56

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.