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.