Hello everybody,
I have written the following query for SQL Server but an error occurs while executing it which is illogical and
does not make sense:
SELECT ac.name AS Column_Name, (SELECT COUNT(DISTINCT ac.name) FROM [Northwind].[Sales].[Orders])
FROM [Northwind].sys.all_columns ac
WHERE object_id = OBJECT_ID('[Northwind].[Sales].[Orders]')
The error is the following famous error:
Column 'Northwind.sys.all_columns.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
It is illogical because the aggregate function 'COUNT' belongs to the subquery, not to the main SELECT statement.
I also tried to trick SQL Server by rewriting the statement like below with 'MyFunction' function but I need to execute a dynamic SQL within that function which I figured out that it is not possible:
SELECT ac.name AS Column_Name, MyFunction(ac.name) FROM [Northwind].[Sales].[Orders])
FROM [Northwind].sys.all_columns ac
WHERE object_id = OBJECT_ID('[Northwind].[Sales].[Orders]')
I also added the 'group by ac.name' clause to the first query which logically is not needed, for test purpose, and either of two scenarios occurs:
The following error occurs:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Which is again illogical because the subquery never returns more than 1 value. That is absurd.
The result is identical for every ac.name in the result grid, which is wrong and unwanted
What can the possible solution and workaround be? I really appreciate your help.
Best regards,
Ali

