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

huangapple
go评论101阅读模式
英文:

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

问题

  1. SELECT TOP (100) 
  2.     DevDate = CAST(a.allDates AS DATE), 
  3.     FailsCount = COUNT(Test_Failure_Mode), 
  4.     TotalCount = COUNT(*), 
  5.     FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*) 
  6. FROM 
  7.     A_Inspections a 
  8. JOIN 
  9.     B_Inspections b ON a.test_ID = b.test_ID 
  10. WHERE 
  11.     DevDate >= '2020-01-01' 
  12. GROUP BY 
  13.     DevDate

看起来我没有正确访问表，出现以下错误：

> 由于它不包含在聚合函数或GROUP BY子句中，所以'column_name'列在选择列表中是无效的。

英文: 
  1. SELECT TOP (100) 
  2.     DevDate = CAST(a.allDates AS DATE), 
  3.     FailsCount = COUNT(Test_Failure_Mode), 
  4.     TotalCount = COUNT(*), 
  5.     FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*) 
  6. FROM 
  7.     A_Inspections a 
  8. JOIN 
  9.     B_Inspections b ON a.test_ID = b.test_ID 
  10. WHERE 
  11.     DevDate >= '2020-01-01' 
  12. GROUP BY 
  13.     DevDate

It looks like I am not accessing the tables properly and I am getting the following error:

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

答案1

得分: 1

你可以通过CROSS APPLY创建的alias进行GROUP BY。

第一个查询：

  1. SELECT top(100) 
  2.     DevDate 
  3.     FailsCount = COUNT(Test_Failure_Mode), 
  4.     TotalCount = COUNT(*), 
  5.     FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*) 
  6.  FROM A_Inspections a 
  7.  JOIN  B_Inspections b ON a.test_ID = b.test_ID 
  8.  CROSS APPLY ( values ( CAST(a.allDates AS DATE) ) ) V(DevDate)
  9.  WHERE DevDate >= '2020-01-01' 
  10.  GROUP BY DevDate

第二个查询：

  1. SELECT top(100) 
  2.     DevDate    = CAST(a.allDates AS DATE)
  3.     FailsCount = COUNT(Test_Failure_Mode), 
  4.     TotalCount = COUNT(*), 
  5.     FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*) 
  6.  FROM A_Inspections a 
  7.  JOIN  B_Inspections b ON a.test_ID = b.test_ID 
  8.  WHERE DevDate >= '2020-01-01' 
  9.  GROUP BY CAST(a.allDates AS DATE)
英文:

You can GROUP BY an alias if it was created via a CROSS APPLY

  1. SELECT top(100) 
  2.       DevDate 
  3.       FailsCount = COUNT(Test_Failure_Mode), 
  4.       TotalCount = COUNT(*), 
  5.       FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*) 
  6.  FROM A_Inspections a 
  7.  JOIN  B_Inspections b ON a.test_ID = b.test_ID 
  8.  CROSS APPLY ( values ( CAST(a.allDates AS DATE) ) ) V(DevDate)
  9.  WHERE DevDate >= '2020-01-01' 
  10.  GROUP BY DevDate

Or you can GROUP BY the expression

  1. SELECT top(100) 
  2.       DevDate    = CAST(a.allDates AS DATE)
  3.       FailsCount = COUNT(Test_Failure_Mode), 
  4.       TotalCount = COUNT(*), 
  5.       FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*) 
  6.  FROM A_Inspections a 
  7.  JOIN  B_Inspections b ON a.test_ID = b.test_ID 
  8.  WHERE DevDate >= '2020-01-01' 
  9.  GROUP BY CAST(a.allDates AS DATE)

huangapple
  • 本文由 发表于 2023年3月7日 08:16:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/75656977.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定