Column must appear in the GROUP BY clause or be used in an aggregate function (PostgreSQL)

3430 views postgresql
-2

I have this database of bank information:

  id    | date     |     asset           
--------+----------+---------------
 1      | 6/30/2001|    333860 
 1      | 3/31/2001|    336896
 1      | 9/30/2001|    349343
 2      | 6/30/2001|    451297
 2      | 3/31/2001|    411421
 2      | 9/30/2001|    430178
 3      | 6/30/2001|    106506
 3      | 3/31/2001|    104196
 3      | 9/30/2001|    106383

I am trying to display the ID for the bank with the second largest total asset. Here is the code I used (similar to this question)

SELECT DISTINCT(id), SUM(asset) AS mv 
FROM bank2001 
WHERE asset NOT IN (SELECT MAX(asset) FROM bank2001)

When I ran the query, I received the following error:

ERROR: column "bank2001.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT DISTINCT(id), MAX(asset) AS mx

I have looked up this error but I can't find anything on how it relates to this problem since I do not have GROUP BY in my code. What does this error mean and how can I go about fixing this issue?

Note: The code should have returned the id of 1.

answered question

You miss the parentheses in from bank2001

I do not have GROUP BY in my code.: that's what the error is complaining about. You must have one, and the ID must be listed in this group by clause.

@dwir182 I forgot to add the parentheses when I copied my code here. Thanks for pointing that out.

1 Answer

0

First Distinct are not Aggregate Function.. It will remove duplicates but not do Aggregate. And you have Sum() function which is Aggregate so it need to Group By

SELECT 
    DISTINCT(id), SUM(asset) AS mv 
FROM 
    bank2001 
WHERE 
    asset NOT IN (SELECT 
                     MAX(asset) 
                  FROM 
                     bank2001)
GROUP BY
     id

posted this

Have an answer?

JD

Please login first before posting an answer.