1

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)

baltiturg
  • 350
  • 2
  • 9

3 Answers3

4

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: 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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Cool but when I do add the output value(find_min) I get another error : "aggregate functions are not allowed in GROUP BY" that's because I can't add to SELECT anything but name and MIN, but what if I want to also show other columns that are unique? like date? – baltiturg Feb 11 '22 at 20:04
  • You add the aggregate to the `SELECT` list, not to the `GROUP BY` clause. How would you form groups based on a sum? – Laurenz Albe Feb 11 '22 at 20:04
  • what if I want to include in my results other columns that are unique? like date? – baltiturg Feb 11 '22 at 20:05
  • 1
    Yes, but which of the many dates from all the rows with the same `name` should be output? – Laurenz Albe Feb 11 '22 at 20:05
  • they are the same! each company has many dates, and for all dates there is a single minimum, so we get many rows with a single name and many dates but the min column is the same for all, so how can I get a result which include min and date column ?\ – baltiturg Feb 11 '22 at 20:07
  • see my edit maybe. – baltiturg Feb 11 '22 at 20:13
  • Actually the part about grouping by the primary key is not a Postgres extension, it **is** defined in the SQL standard. –  Feb 12 '22 at 16:05
  • @a_horse_with_no_name I had a brief look, but didn't find it. But I trust you on that. – Laurenz Albe Feb 12 '22 at 21:48
  • There is a reference to it [in the manual](https://www.postgresql.org/docs/current/sql-select.html#id-1.9.3.171.10.10) –  Feb 12 '22 at 21:56
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

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • so basically if I add to SELECT value column he wouldn't know what to do because there are many rows with different values ? but what if I want to include more columns in my result? like date? – baltiturg Feb 11 '22 at 20:02
  • @baltiturg you can share your sample data and expected output in separate question – Zaynul Abadin Tuhin Feb 11 '22 at 20:17
  • I can only ask once in a while here. still struggle to find minimum and include dates column, maybe you could help? how can I use your answer adding a column name date for the result. ? (each name has many dates) – baltiturg Feb 11 '22 at 20:25
1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
masterjoda99
  • 84
  • 7
  • so why I can't GROUP BY name, min ? they are identical for all rows, and this gives error as well – baltiturg Feb 11 '22 at 20:11
  • @baltiturg you can group by name or group by value, you cant group by both or the answer to the min operation would be trivial. Even if you have in Name and Value all the same lines, this shouldnt work, because it is not a trivial question to be answered by the database – masterjoda99 Feb 11 '22 at 20:44