user1236373 user1236373 - 1 year ago 156
SQL Question

SQL Server, Returning a summed data set grouped by a single column for a merge query

I have a data set like the following:

BaseID| SecondaryTypeID| Value
------------------------------
1 5 1
1 5 3
1 5 8


What I'd like to get is something like:

BaseID| SecondaryTypeID| Value
------------------------------
1 5 12


Now, I can return that data set with the following:

select BaseID,
SecondaryTypeID,
(select sum(Value) from TableA) as SummedValue
from TableA
group by BaseID, SecondaryTypeID


However, I can't do that as part of a merge query, the inner select breaks the merge with an error that looks like this:


Query processor could not produce a query plan because of the hints
defined in this query. Resubmit the query without specifying any hints
and without using SET FORCEPLAN.


Any thoughts on how to resolve this would be gratefully received!

Answer Source

Why would you use a subquery? Just do:

select BaseID, SecondaryTypeID, sum(Value) as SummedValue
from TableA
group by BaseID, SecondaryTypeID;

If you have multiple rows in the result set and want the overall sum, then use window functions:

select BaseID, SecondaryTypeID,
       sum(sum(Value)) over () as SummedValue
from TableA
group by BaseID, SecondaryTypeID;

This is what your query really is saying to do. I doubt it is what you want.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download