CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
eggbert
eggbert

Reputation: 3233

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

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.

Upvotes: 2

Views: 20065

Answers (6)

Dman Cannon
Dman Cannon

Reputation: 126

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

Upvotes: -1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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:

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;

Upvotes: 6

Pரதீப்
Pரதீப்

Reputation: 93704

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

Upvotes: 2

dreamgt
dreamgt

Reputation: 137

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)

Upvotes: 0

dmnoguera
dmnoguera

Reputation: 46

Use:

SELECT
  DISTINCT(test)
FROM aatest

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

Upvotes: -2

radar
radar

Reputation: 13425

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

Upvotes: 0

Related Questions