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

Question

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.

Answer 1

Aggregates doesn't work on a complete result, they only work on a group in a result.

Consider a table containing:

Person   Pet -------- -------- Amy      Cat Amy      Dog Amy      Canary Dave     Dog Susan    Snake Susan    Spider 

If you use a query that groups on Person, it will divide the data into these groups:

Amy:   Amy    Cat   Amy    Dog   Amy    Canary Dave:   Dave   Dog Susan:   Susan  Snake   Susan  Spider 

If you use an aggreage, for exmple the count aggregate, it will produce one result for each group:

Amy:   Amy    Cat   Amy    Dog   Amy    Canary    count(*) = 3 Dave:   Dave   Dog       count(*) = 1 Susan:   Susan  Snake   Susan  Spider    count(*) = 2 

So, the query select Person, count(*) from People group by Person gives you one record for each group:

Amy    3 Dave   1 Susan  2 

If you try to get the Pet field in the result also, that doesn't work because there may be multiple values for that field in each group.

(Some databases, like MySQL, does allow that anyway, and just returns any random value from within the group, and it's your responsibility to know if the result is sensible or not.)

If you use an aggregate, but doesn't specify any grouping, the query will still be grouped, and the entire result is a single group. So the query select count(*) from Person will create a single group containing all records, and the aggregate can count the records in that group. The result contains one row from each group, and as there is only one group, there will be one row in the result.

Answer 2

Think about it this way: when you call COUNT without grouping, it "collapses" the table to a single group making it impossible to access the individual items within a group in a select clause.

You can still get your result using a subquery or a cross join:

    SELECT p1.Name, COUNT(p2.Name) AS Surname FROM People p1 CROSS JOIN People p2 GROUP BY p1.Name      SELECT Name, (SELECT COUNT(Name) FROM People) AS Surname FROM People