group by and select other columns that are not in aggreate functions works?

Question

I know that in a query with group by clause, you can only select the columns in group by clause or using aggregate functions. But in my case below it works (project_name column). I want to know the logic. And under which conditions this works?

project Table

id	name	xxxxx.
p001	ML1	--------
p002	AI2	--------

uer_liked_projects Table

project_id	user_id	timestamp
p001	u001	--------
p001	u002	--------
p002	u001	--------
p002	u003	--------

My Query:

SELECT proj.id, proj.name, COUNT(*) as count_user
FROM project proj
JOIN uer_liked_projects ulp
ON proj.id = ulp.project_id
GROUP BY proj.id

Result:

id	name.	count_user
p001	ML1	3
p002	AI2	2

I suppose that the groupby is applied to primary_key, and in this case, you could select any other columns?

Answer 1

Postgres implements this behavior which is defined in the ANSI-92 SQL standard. When aggregating by a column which is unique, any other column can also be selected, because those columns are said to be functionally dependent on the unique column(s) which appear in the GROUP BY clause. Put another way, if we know the id value for a record in the project table, we therefore also know the value for any other column from that table. Hence, in your query, it is legitimate to aggregate only by the project id, but also select the name.

However, on some other databases, such as Oracle and SQL Server, this behavior is not supported. There, you would have to use the following version of your query:

SELECT proj.id, proj.name, COUNT(*) AS count_user
FROM project proj
INNER JOIN uer_liked_projects ulp
    ON proj.id = ulp.project_id
GROUP BY proj.id, proj.name;

Here, we needed to add the name column to the GROUP BY clause to get the code to compile.