"Leif S" <Le***@discussions.microsoft.com> wrote in message
news:F5F8212C-084F-42ED-8989-8C4D831907F6@microsoft.com...
> In a sp I aggregate values in a union:
>
> SELECT O.operations_code, SUM(ISNULL(OL.operations_duration, 0)) AS Accum
> FROM dbo.dpr_report R
> INNER JOIN dbo.dpr_operations_report_line OL ON R.dpr_report_id =
> OL.dpr_report_id
> RIGHT OUTER JOIN dbo.vessel_operations_code O ON OL.operations_code_id =
> O.operations_code_id AND R.vessel_assignment_id = @assignmentId
> GROUP BY O.operations_code
> UNION
> SELECT   O.operations_code, SUM(ISNULL(A.operation_duration, 0)) AS Accum
> FROM dbo.vessel_assignment V
> INNER JOIN dbo.accum_operations A ON V.vessel_assignment_id =
> A.vessel_assignment_id RIGHT OUTER JOIN dbo.vessel_operations_code O ON
> A.operation_code_id = O.operations_code_id AND V.vessel_assignment_id =
> @assignmentId
> GROUP BY O.operations_code
>
> The result set is like this:
> operations_code Accum
> M  0
> T  100
> T  1476
> O  200
> O  8071
> S  0
> W  0
> C  0
> C  10
> B  5
> B  533
> R  0
>
> The challenge is that I want a result-set where the corresponding
> operations
> codes (the Ts the Os etc)from the two selects are summed and not split as
> above.
>
> Can anyone advice me on this?
> --
> Systems Analyst