英文:
Column ' column_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
问题
SELECT TOP (100)
DevDate = CAST(a.allDates AS DATE),
FailsCount = COUNT(Test_Failure_Mode),
TotalCount = COUNT(*),
FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*)
FROM
A_Inspections a
JOIN
B_Inspections b ON a.test_ID = b.test_ID
WHERE
DevDate >= '2020-01-01'
GROUP BY
DevDate
看起来我没有正确访问表，出现以下错误：
> 由于它不包含在聚合函数或GROUP BY子句中，所以'column_name'列在选择列表中是无效的。
英文:
SELECT TOP (100)
DevDate = CAST(a.allDates AS DATE),
FailsCount = COUNT(Test_Failure_Mode),
TotalCount = COUNT(*),
FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*)
FROM
A_Inspections a
JOIN
B_Inspections b ON a.test_ID = b.test_ID
WHERE
DevDate >= '2020-01-01'
GROUP BY
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。
第一个查询：
SELECT top(100)
DevDate
FailsCount = COUNT(Test_Failure_Mode),
TotalCount = COUNT(*),
FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*)
FROM A_Inspections a
JOIN B_Inspections b ON a.test_ID = b.test_ID
CROSS APPLY ( values ( CAST(a.allDates AS DATE) ) ) V(DevDate)
WHERE DevDate >= '2020-01-01'
GROUP BY DevDate
第二个查询：
SELECT top(100)
DevDate = CAST(a.allDates AS DATE)
FailsCount = COUNT(Test_Failure_Mode),
TotalCount = COUNT(*),
FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*)
FROM A_Inspections a
JOIN B_Inspections b ON a.test_ID = b.test_ID
WHERE DevDate >= '2020-01-01'
GROUP BY CAST(a.allDates AS DATE)
英文:
You can GROUP BY an alias if it was created via a CROSS APPLY
SELECT top(100)
DevDate
FailsCount = COUNT(Test_Failure_Mode),
TotalCount = COUNT(*),
FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*)
FROM A_Inspections a
JOIN B_Inspections b ON a.test_ID = b.test_ID
CROSS APPLY ( values ( CAST(a.allDates AS DATE) ) ) V(DevDate)
WHERE DevDate >= '2020-01-01'
GROUP BY DevDate
Or you can GROUP BY the expression
SELECT top(100)
DevDate = CAST(a.allDates AS DATE)
FailsCount = COUNT(Test_Failure_Mode),
TotalCount = COUNT(*),
FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*)
FROM A_Inspections a
JOIN B_Inspections b ON a.test_ID = b.test_ID
WHERE DevDate >= '2020-01-01'
GROUP BY CAST(a.allDates AS DATE)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论