Why does SQL Server require an aggregate function when grouping by primary key? -

let's have 2 tables

create table users ( name varchar(50) primary key, gender integer ); create table likes ( username varchar(50), object varchar(50) ); 

now, want find out genders , number of likes each user

select      u.name, u.gender, count(*)  users u inner join likes l on u.name = l.username group u.name 

here i'm grouping primary key, meaning there 1 user row per group. however, sql server gives me following error

column 'users.gender' invalid in select list because not contained in either aggregate function or group clause.

why complaining? there way can achieve desired behavior?

edit: apparently behavior columns without aggregate function must added group by clause. guess true question is: why behave way?

the behavior describe consistent ansi standard. if group of columns define unique row, other values "functionally dependent" on columns. can have no other values. these other columns can included in select without including them in group by.

the way functional dependencies identified , enforced through primary , unique keys.

so, desire include name quite reasonable. sql server -- , other databases not support (i think functionality optional ansi-compliance). postgres has supported functional dependencies since version 9.1. mysql "fixing" current group by support in 5.7.