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:
- Why I have to include in GROUP BY everything? what is the logic?
- Why is this not working?
- 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)
Laurenz Albe :
That is actually easy to understand.\nWhen 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.\nEven 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.\nThere is actually one exception: 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.",
2022-02-11T19:36:14
Zaynul Abadin Tuhin :
try like below\nSELECT name,\nMIN(value) as find_min\nFROM history\nWHERE date_num >= 1609459200 AND date_num <= 1640995200\nGROUP BY name\n\nI removed name in ('A') because your are searching for all name min value so it will restrict just A",
2022-02-11T19:31:57