Why can't you mix Aggregate values and Non-Aggregate values in a single SELECT?

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.

20
задан TarkaDaal 7 May 2011 в 08:55
поделиться