I know that if you have one aggregate function in a SELECT statement, then all the other values in the statement must be either aggregate functions, or listed in a GROUP BY clause. I don't understand why that's the case.
If I do:
SELECT Name, 'Jones' AS Surname FROM People
I get:
NAME SURNAME
Dave Jones
Susan Jones
Amy Jones
So, the DBMS has taken a value from each row, and appended a single value to it in the result set. That's fine. But if that works, why can't I do:
SELECT Name, COUNT(Name) AS Surname FROM People
It seems like the same idea, take a value from each row and append a single value. But instead of:
NAME SURNAME
Dave 3
Susan 3
Amy 3
I get:
You tried to execute a query that does not include the specified expression 'ContactName' as part of an aggregate function.
I know it's not allowed, but the two circumstances seem so similar that I don't understand why. Is it to make the DBMS easier to implement? If anyone can explain to me why it doesn't work like I think it should, I'd be very grateful.