MS SQL Count and Case statements not working

Ignition
1 
	count(case when Telesis_Stamp='' then null else 1 end) as Passed,
		count(case when Telesis_Stamp='' then 1 else null end) as Failed,
	count(*) as Total
FROM PartData_GKN05_C
WHERE Infeed_Pick_Time >= DATEADD(day,-7, GETDATE()) 
ALTER TABLE  PartData_GKN05_C Add Total_Passed_Remeasures int
ALTER TABLE  PartData_GKN05_C Add Total_Good_Parts int
ALTER TABLE  PartData_GKN05_C Add Total_Rejects int
ALTER TABLE  PartData_GKN05_C Add Total_Failed_Remeasures int

If I remove the count statements, the error goes away…

Error

2

When you include COUNT(*) statements, you make a summary (aggregate) over different rows.

For this to have a meaning, you should aggregate over all columns at once, or group them by certain columns (f.e. group a count by article, to have a number of items for an article).

If you don’t aggregate and don’t group by a column, it makes no sense to include the column in your query.

This is a generic SQL error, nothing Ignition specific. See also https://stackoverflow.com/questions/18258704/sql-server-column-invalid-in-the-select-list-because-it-is-not-contained-in-e for example.

PS. please write your code in triple backquotes for clarity.

1 Like