ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

Question

Example data:

test,test2
----------
a,qwer
b,wert
c,erty
d,rtuy
d,tyui
e,yuio
e,uiop

I am trying to write a query which returns unique values in a table:

SELECT
  test

FROM aatest
GROUP BY test
HAVING (COUNT(test) = 1)

This works and returns a,b and c because d and e aren't unique.

But I want to print out test2 as well so I tried:

select 
test, 
test2
from aatest
GROUP BY test
HAVING (COUNT(test) = 1)

But I get the error.

Do we assume that the table does not have a primary key or any other combination of unique columns? — Erwin Brandstetter, Oct 30 '14 at 16:52

score 6 · Answer 1 · edited May 23 '17 at 12:23

Wrap the other column(s) in an aggregate function like min() or max(). Since there is only a single row per aggregate, it does not matter which:

SELECT test, min(test2) AS test2
FROM   aatest
GROUP  BY test
HAVING count(*) = 1;

For lots of columns this is simpler:

SELECT t1.*  -- return all columns
FROM   aatest t1
LEFT   JOIN aatest t2 ON t2.test = t1.test AND t2.ctid <> t1.ctid
WHERE  t2.test IS NULL;

Assuming there is no PK or any other unique combination of columns we could use to unambiguously identify a row, I use the internal (Postgres specific!) tuple ID ctid. Related:

In-order sequence generation

If (test, test2) is unique:

SELECT t1.*  -- return all columns
FROM   aatest t1
LEFT   JOIN aatest t2 ON t2.test = t1.test AND t2.test2 <> t1.test2
WHERE  t2.test IS NULL;

Pரதீப் · Answer 2 · 2014-10-30T17:04:26.673

2

Error message clearly explains the problem. The column list in select statement must be present in the group by unless u have an aggregate function(max,min,sum,count,etc..) on it Try this, this is what you are trying to achieve

SELECT a.test,
       a.test2
FROM   aatest a
       JOIN (SELECT test
             FROM   aatest
             GROUP  BY test
             HAVING ( Count(test) = 1 )) b
         ON a.test = b.test

edited Oct 30 '14 at 17:04

answered Oct 30 '14 at 16:12

Pரதீப்

91,748
19
131
172

Use `USING (test)` or you have to table-qualify the ambiguous column name in the SELECT list: `a.test`. – Erwin Brandstetter Oct 30 '14 at 17:03
@ErwinBrandstetter - Yes didn't check properly. Corrected now and thanks for pointing out – Pரதீப் Oct 30 '14 at 17:05

score 0 · Answer 3 · answered Oct 30 '14 at 16:12

0

wrap it in a subquery

SELECT * FROM aatest
JOIN (
SELECT
  test
FROM aatest
GROUP BY test
HAVING (COUNT(test) = 1)
) T
on aatest.test = T.test

answered Oct 30 '14 at 16:12

radar

13,270
2
25
33

score 0 · Answer 4 · answered Oct 30 '14 at 16:17

What's happening is that you are grouping all repeated values in Test column, but if the values in Test2 are unique, where are they going to go? If the values in test two were numbers you could select them in the statement with an aggregate function such as count():

select 
test, 
count(test2)
from aatest
GROUP BY test
HAVING (COUNT(test) = 1)

score -1 · Answer 5 · answered Aug 23 '20 at 07:10

-1

You need to have the column test2 in group by clause. Are else use aggregate function for that particular column

answered Aug 23 '20 at 07:10

Dman Cannon

116
7

score -2 · Answer 6 · answered Oct 30 '14 at 16:13

-2

Use:

SELECT
  DISTINCT(test)
FROM aatest

http://www.postgresql.org/docs/9.0/static/sql-select.html

answered Oct 30 '14 at 16:13

dmnoguera

46
3

1

`distinct` is **not** a function – Oct 30 '14 at 16:51

ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

6 Answers6