none
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

    问题

  • can't seem to know how to get this query working, if i remove the case statement from the group by clause, it still doesnt work

    select
    	division
    	, report_name
    	, datename(m, export) as month
    	, DATENAME(yyyy, export) +'/'+ RIGHT('00' + CAST(DATEPART(M, export) AS VARCHAR(2)), 2) monorder
    	, case when datediff(hh, export, transcribed) <24 then count(*) end as [<24]
    	, case when datediff(hh, export, transcribed) <48 then count(*) end as [<48]
    	, case when datediff(hh, export, transcribed) >48 then count(*) end as [>48]
    from #TempTable ws
    	inner join dbo.Depts Dept on ws.department = dept.dept_name and 1 = dept.[use]
    where export is not null					
    	and transcribed is not null	
    group by
    	division
    	, report_name
    	, datename(m, export)
    	, DATENAME(yyyy, export) +'/'+ RIGHT('00' + CAST(DATEPART(M, export) AS VARCHAR(2)), 2)
    	, case when datediff(hh, export, transcribed) <24 then count(*) end
    	, case when datediff(hh, export, transcribed) <48 then count(*) end
    	, case when datediff(hh, export, transcribed) >48 then count(*) end

    Msg 144, Level 15, State 1, Line 1

    Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

    2012年3月16日 10:18

答案

  • Most probably you mean

    select
    	division
    	, report_name
    	, datename(m, export) as month
    	, DATENAME(yyyy, export) +'/'+ RIGHT('00' + CAST(DATEPART(M, export) AS VARCHAR(2)), 2) monorder
    	, count( case when datediff(hh, export, transcribed) <24 then 1 end) as [<24]
    	, count( case when datediff(hh, export, transcribed) <48 then 1 end) as [<48]
    	, count(case when datediff(hh, export, transcribed) >48 then 1 end) as [>48]
    from #TempTable ws
    	inner join dbo.Depts Dept on ws.department = dept.dept_name and 1 = dept.[use]
    where export is not null					
    	and transcribed is not null	
    group by
    	division
    	, report_name
    	, datename(m, export)
    	, DATENAME(yyyy, export) +'/'+ RIGHT('00' + CAST(DATEPART(M, export) AS VARCHAR(2)), 2)
    


    Serg

    • 已标记为答案 Sam233 2012年3月16日 11:53
    2012年3月16日 10:29

全部回复

  • Most probably you mean

    select
    	division
    	, report_name
    	, datename(m, export) as month
    	, DATENAME(yyyy, export) +'/'+ RIGHT('00' + CAST(DATEPART(M, export) AS VARCHAR(2)), 2) monorder
    	, count( case when datediff(hh, export, transcribed) <24 then 1 end) as [<24]
    	, count( case when datediff(hh, export, transcribed) <48 then 1 end) as [<48]
    	, count(case when datediff(hh, export, transcribed) >48 then 1 end) as [>48]
    from #TempTable ws
    	inner join dbo.Depts Dept on ws.department = dept.dept_name and 1 = dept.[use]
    where export is not null					
    	and transcribed is not null	
    group by
    	division
    	, report_name
    	, datename(m, export)
    	, DATENAME(yyyy, export) +'/'+ RIGHT('00' + CAST(DATEPART(M, export) AS VARCHAR(2)), 2)
    


    Serg

    • 已标记为答案 Sam233 2012年3月16日 11:53
    2012年3月16日 10:29
  • when running this i get the following error message

    Msg 8120, Level 16, State 1, Line 1

    Column '#TempTable.export' 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 1

    Column '#TempTable.transcribed' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    2012年3月16日 10:36
  • Pls show DDL for both tables involved.

    Serg

    2012年3月16日 11:29
  • When you group, any column not contained in an aggregate function MUST be in the group-by list.  Did you add the temp table columns to the above query?  If so, you need to add them to the grouping list in order for it to compile.
    2012年3月16日 11:57