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:29

   

   
  

  0

  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:36

   

   
  

  0

  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日 11:29

   

   
  

  0
  Pls show DDL for both tables involved.

  ---

  Serg

  • 回复
  • 引用

   
• 2012年3月16日 11:57

   

   
  

  0
  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.
  • 回复
  • 引用