none
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    Question

  • Good afternoon,

    I'm totally stuck trying to figure out how to write this stored procedure so I can use it in Report Builder 3.0 (Using SQL Server 2008 R2)

    When I try and create the stored procedure I get this error:

    Msg 130, Level 15, State 1, Procedure SP_NO_ACTIVITY, Line 19
    Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    Here is the stored procedure currently:

    PROCEDURE SP_NO_ACTIVITY
    
    	@Sdate1 date,
    	@Edate1 date,
    	@AE nchar(2),
    	@showtitle nvarchar(max)
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    
    	SELECT vw_ACTIVE.[Contact], vw_ACTIVE.[Aff Code], vw_ACTIVE.[Mso/System], vw_ACTIVE.[Product Code], vw_ACTIVE.[Show Title], vw_ACTIVE.[Phone], vw_ACTIVE.[Date Sent], SUM((SELECT [SumOfGross] FROM [dbo].[fn_ORDERS_SUB2] (@Sdate1,@Edate1))) AS [Revenue], MAX(vw_ORDERS_SUB.[MaxOfOrdDate]) AS [Last Order Date]
    	FROM vw_ACTIVE LEFT JOIN vw_ORDERS_SUB ON vw_ACTIVE.[Phone] = vw_ORDERS_SUB.[Phone] AND vw_ACTIVE.[Product Code] = vw_ORDERS_SUB.[Product] AND vw_ACTIVE.[Aff Code] = vw_ORDERS_SUB.[Aff] LEFT JOIN [dbo].[fn_ORDERS_SUB2] (@Sdate1,@Edate1) ON vw_ORDERS_SUB.[Phone] = (SELECT [Phone] FROM [dbo].[fn_ORDERS_SUB2] (@Sdate1,@Edate1)) AND vw_ORDERS_SUB.[Product] = (SELECT [Product] FROM [dbo].[fn_ORDERS_SUB2] (@Sdate1,@Edate1)) AND vw_ORDERS_SUB.[Aff] = (SELECT [Aff] FROM [dbo].[fn_ORDERS_SUB2] (@Sdate1,@Edate1))
    	GROUP BY vw_ACTIVE.[Contact], vw_ACTIVE.[Aff Code], vw_ACTIVE.[Mso/System], vw_ACTIVE.[Product Code], vw_ACTIVE.[Show Title], vw_ACTIVE.[Phone], vw_ACTIVE.[Date Sent]
    	HAVING vw_ACTIVE.[Contact] LIKE @AE AND vw_ACTIVE.[Show Title] LIKE @showtitle
    END

    And here is the Function that is used in the Stored Procedure:

    FUNCTION fn_ORDERS_SUB2
    (	
    	@Sdate date,
    	@Edate date
    )
    RETURNS TABLE 
    AS
    RETURN 
    (
    	SELECT TeleOrders.Aff, TeleOrders.[Invoice Date], TeleOrders.OrdDate, Sum(TeleOrders.Gross) AS SumOfGross, TeleOrders.Product, TeleOrders.[Product Name], TeleOrders.Phone, TeleOrders.[Call Type]
    	FROM TeleOrders
    	GROUP BY TeleOrders.Aff, TeleOrders.[Invoice Date], TeleOrders.OrdDate, TeleOrders.Product, TeleOrders.[Product Name], TeleOrders.Phone, TeleOrders.[Call Type]
    	HAVING (((TeleOrders.Aff) Like 'T%') AND ((TeleOrders.[Invoice Date]) Between @Sdate And @Edate AND ((TeleOrders.[Call Type])='R'))
    )

    I know that the issue is that I am using SUM on the Function, which also contains a SUM, but I'm struggling to come up with a way to re-write this.

    Any help or suggestions would be greatly appreciated.

    Thanks!

    Tuesday, March 20, 2012 10:40 PM

Answers

  • You can first perform a query with a user defined function as a derived table and then do the grouping.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Daniel Tieman Wednesday, March 21, 2012 12:21 AM
    Tuesday, March 20, 2012 10:42 PM

All replies

  • You can first perform a query with a user defined function as a derived table and then do the grouping.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Daniel Tieman Wednesday, March 21, 2012 12:21 AM
    Tuesday, March 20, 2012 10:42 PM
  • In your stored proc it seems like your HAVING clause could be changed to a WHERE clause

    John

    http://knowledgy.org

    Tuesday, March 20, 2012 11:24 PM
  • Ok, so if I take the function and make it a derived table:

    	FROM 
    		(
    			SELECT TeleOrders.Aff, TeleOrders.[Invoice Date], TeleOrders.OrdDate, Sum(TeleOrders.Gross) AS SumOfGross, TeleOrders.Product, TeleOrders.[Product Name], TeleOrders.Phone, TeleOrders.[Call Type]
    			FROM TeleOrders
    			GROUP BY TeleOrders.Aff, TeleOrders.[Invoice Date], TeleOrders.OrdDate, TeleOrders.Product, TeleOrders.[Product Name], TeleOrders.Phone, TeleOrders.[Call Type]
    			HAVING (((TeleOrders.Aff) Like 'T%') AND ((TeleOrders.[Invoice Date]) Between @Sdate And @Edate AND ((TeleOrders.[Call Type])='R'))
    		)
    	AS SUB2

    How do I work in all the JOIN statements?

    I'm sorry if this is basic stuff, I'm still getting my sea legs with SQL.

    Tuesday, March 20, 2012 11:44 PM
  • I figured it out.  Fantastic!

    PROCEDURE SP_NO_ACTIVITY
    
    	@Sdate date,
    	@Edate date,
    	@AE nchar(2),
    	@showtitle nvarchar(max)
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    
    	SELECT vw_ACTIVE.[Contact], vw_ACTIVE.[Aff Code], vw_ACTIVE.[Mso/System], vw_ACTIVE.[Product Code], vw_ACTIVE.[Show Title], vw_ACTIVE.[Phone], vw_ACTIVE.[Date Sent], SUM(SUB2.SumOfGross) AS [Revenue], MAX(vw_ORDERS_SUB.[MaxOfOrdDate]) AS [Last Order Date]
    	FROM vw_ACTIVE LEFT JOIN vw_ORDERS_SUB ON vw_ACTIVE.[Phone] = vw_ORDERS_SUB.[Phone] AND vw_ACTIVE.[Product Code] = vw_ORDERS_SUB.[Product] AND vw_ACTIVE.[Aff Code] = vw_ORDERS_SUB.[Aff] LEFT JOIN
    			(
    			SELECT TeleOrders.Aff, TeleOrders.[Invoice Date], TeleOrders.OrdDate, Sum(TeleOrders.Gross) AS SumOfGross, TeleOrders.Product, TeleOrders.[Product Name], TeleOrders.Phone, TeleOrders.[Call Type]
    			FROM TeleOrders
    			GROUP BY TeleOrders.Aff, TeleOrders.[Invoice Date], TeleOrders.OrdDate, TeleOrders.Product, TeleOrders.[Product Name], TeleOrders.Phone, TeleOrders.[Call Type]
    			HAVING (((TeleOrders.Aff) Like 'T%') AND ((TeleOrders.[Invoice Date]) Between @Sdate And @Edate AND ((TeleOrders.[Call Type])='R'))
    			)
    	AS SUB2
    	ON vw_ORDERS_SUB.[Phone] = SUB2.[Phone] AND vw_ORDERS_SUB.[Product] = SUB2.[Product] AND vw_ORDERS_SUB.[Aff] = SUB2.[Aff]
    	GROUP BY vw_ACTIVE.[Contact], vw_ACTIVE.[Aff Code], vw_ACTIVE.[Mso/System], vw_ACTIVE.[Product Code], vw_ACTIVE.[Show Title], vw_ACTIVE.[Phone], vw_ACTIVE.[Date Sent]
    	HAVING vw_ACTIVE.[Contact] LIKE @AE AND vw_ACTIVE.[Show Title] LIKE @showtitle
    END

    Thanks so much for the help!
    Wednesday, March 21, 2012 12:20 AM
  • It's impossible to understand what you're trying to do because your
    subqueries (either version) are not correlated in any way with their
    surrounding.  In the first version, you have to constant parameters to your
    function call: fn_Orders_Sub2(@Sdate1,@Edate1); which mean that the same
    value will always be returned for each row because there is no relationship
    between a row and a call to the function.

    Your second version suffer the same problem: you didn't define any
    correlation between your main query and your subquery.  Without this
    information, it's impossible to understand what you are trying to calculate
    and therefore, we cannot propose you a solution.

    Furthermore, you have two other problems here:

    1- What the point of returning a table with many columns if you are only
    interested in the value of SumOfGross?  Furthermore, if your function is
    correlated, a single value of SumOfGross should be returned instead of table
    of SumOfGross; therefore, a scalar function would be more appropriate here.

    2- The HAVING conditions should be moved to the WHERE part because these
    conditions are not related to the result of the Group BY.  Your query is
    still working correctly but it makes it harder to understand when you write
    it this way.

    Wednesday, March 21, 2012 12:23 AM