Why column must appear in the GROUP BY?

Question

I have this:

SELECT name, value,
       MIN(value) as find_min
FROM history
WHERE date_num >= 1609459200 
  AND date_num <= 1640995200 
  AND name IN('A')
GROUP BY name

Trying to get the minimum value between dates for each subject separately :

name value
A.    3
B     4
C     9
A     0
C     2

I keep getting this popular error:

column "history.value" must appear in the GROUP BY clause or be used in an aggregate function

I read this must appear in the GROUP BY clause or be used in an aggregate function

and I still do not understand:

1. Why I have to include in GROUP BY everything? what is the logic?
2. Why is this not working?
3. is Min() over (partition by name) better, and if so, how can I get only a single result per name?

EDIT:

If I try:GROUP BY name, find_min it will fail as well, even though in this case he can produce a unique result (the all the same)

Answer 1

try like below

SELECT name,
MIN(value) as find_min
FROM history
WHERE date_num >= 1609459200 AND date_num <= 1640995200
GROUP BY name

I removed name in ('A') because your are searching for all name min value so it will restrict just A

Answer 2

To answer your question, GROUP BY groups similar data in a table. For example this table:

A B C
a d 1
a k 2
b d 3

And you have the query:

SELECT A, B, MIN(C)
FROM t
GROUP BY A

and this would not work you can't give a decisive answer what to do with the entry a k 2 because you don't group by Column B, but you group by column A, is there now two entries but they are different. Therefore you have to group by all non min,max,sum,etc. columns.