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)

CodePudding user response：

That is actually easy to understand.

When you say GROUP BY name, all rows where name is the same are grouped together to form a single result row. Now the original table could contain two rows with the same name, but different value. If you add value to the SELECT list, which of those should be output? On the other hand, determining min(value) for each group is no problem.

Even if there is only a single value for the whole group (like with your find_min), you have to add the column to GROUP BY.

There is actually one exception (this is a PostgreSQL extension of the SQL standard): if the primary key of a table is in the GROUP BY clause, other columns from that table need not be in GROUP BY, because this proves automatically that there can be no different values.

CodePudding user response：

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

CodePudding user response：

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 diffrent. Therefore you have to group by all non min,max,sum,etc. columns.