CODEWITHSUNDEEP
sql-server
Charles
Charles

Reputation: 29

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.

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

Upvotes: 1

Views: 80

Answers (1)

S3S
S3S

Reputation: 25132

Since this can change with more wonum being added, I'd have this as a 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

Upvotes: 1

Related Questions