asked
Cannot perform an aggregate function on an expression containing an aggregate or a subquery
Can you help me rewrite this query. I can make this work by writing an outer query and applying the sum outside, but that query is slower. If we can figure this one out, it can solve many of the slow query issues on the reporting side. ============= **QUERY** ============= SELECT top 250000 x1.wo_ref AS [z13031322175522447287ca90], sum( ( select x.approved_ts_amount from dbo.woccsr_ts_amt(20,x3.wo_id,x3.cc_id, x3.gl_id ) as x ) ) AS [z13031322175522447289ca90] FROM dbo.wo AS x1 WITH (NOLOCK) INNER JOIN dbo.wocc AS x3 WITH (NOLOCK) ON x1.wo_id = x3.wo_id WHERE status != 17 and x1.b_code in ('GOOG') GROUP BY x1.wo_ref ============= **FUNCTION** ============= CREATE function [dbo].[woccsr_ts_amt] (@wo_id varchar(24), @cc_id varchar(24), @gl_id varchar(24)) returns TABLE as RETURN select sum((case when nr.status in (1, 3, 44, 52) then idr.detail_amount + idr.crdb_adj_amount else 0 end)) as approved_ts_amount from dbo.pay_detail (nolock) as idr inner join dbo.ts (nolock) nr on nr.ts_id = idr.incurred_id where nr.wo_id = @wo_id and idr.cc_id = @cc_id and idr.gl_id = @gl_id Table structure, data definition not available. Thanks.
Because you are using an inlined table-valued function, the optimizer treats it almost like a view and expands the definition out to use the underlying objects and their statistics - this is why you get the aggregate on aggregate error. Without the tables and data it's difficult to say, but I'd be tempted to remove this double sum() by either 1. change the function to return data at the `wo_ref` level negating the need for sum in the outer query, or 2. change the function to not aggregate - i.e. retrun data at the `wo_id` level, or 3. remove the need for a function altogether and write the query with an in-line derived view (using either of the previous 2 options) As an aside, why do you pass 4 arguments to the function, yet there are only 3 defined?
13 People are following this question.
