Getting errors when I try to group by CTEs I would like to do a group by on object_name on my last cte in this query but I get the following error : 

Column 'CTE1.TimesExecuted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8120, Level 16, State 1, Line 85 Column 'CTE1.CountExec' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.




WITH CTE1 AS(
    select  Object_name
            , max(Date_of_Object_Analysis) as MaxDate
            ,count(Object_name) as CountExec
            ,min(Date_of_Object_Analysis) as MinDate
            , SUM([Number_Times_Executed]) AS TimesExecuted 
            from
    _Admin_Database_Objects_Analysis_Tbl
    --where Object_Name = '_Admin_Client_Bing_GeoCordinates_Tbl'
    Group by Object_name
    )
    ,
    CTE2 AS (
            SELECT c1.Object_name 
            ,cast(c1.CountExec as int)*cast(c1.CountExec as int ) as CountMultiplied
            ,c1.CountExec+c1.CountExec+c1.CountExec+c1.CountExec as Addedup
            FROM _Admin_Database_Objects_Analysis_Tbl c2
            iNNER join CTE1 c1
            on c1.Object_name= c2.Object_Name

    ),
    --select * from CTE2 

    CTE3 AS (

        SELECT c3.Object_name 

            --,c2.CountMultiplied/c1.CountExec as Final_result
            --,c2.CountMultiplied
            ,c1.TimesExecuted/c1.CountExec as New_new_result
            --,c2.Addedup
            ,c1.MinDate
            FROM _Admin_Database_Objects_Analysis_Tbl c3
            INNER join CTE2 c2
            on c3.Object_name= c2.Object_Name
            INNER JOIN CTE1 c1
            on c3.Object_Name = c1.Object_Name
    Group by c3.Object_name 

    )select * from CTE3
In your first CTE you grouped by Object_Name and all of the other entries were contained within an aggregate.  In your last CTE that isn't the case.

    SELECT c3.Object_name 
        ,c1.TimesExecuted/c1.CountExec as New_new_result
        ,c1.MinDate


I've removed the commented lines for clarity, but you'll notice that none of these are contained within an aggregate. (MIN, MAX, AVG, etc).  If you want to group by c3.Object_name you'll need to put something around the other fields.  For example:

    SELECT c3.Object_name 
        ,SUM(c1.TimesExecuted/c1.CountExec) as New_new_result
        ,MIN(c1.MinDate)