“column must appear in the GROUP BY clause or be used in an aggregate function”

Question

I have this query in postgresql, what i expected was a table with two columns. one called counter and the other called forn_primario. But instead i get this error: "column "forn_primario" must appear in the GROUP BY clause or be used in an aggregate function"

Select MAX(contador) as counter, forn_primario
From(
Select COUNT(DISTINCT categoria) as contador, forn_primario
From produto
Group by forn_primario)t

Also after this query

Select COUNT(DISTINCT categoria) as contador, forn_primario
From produto
Group by forn_primario

I get this table

 contador | forn_primario 
----------+---------------
        1 | 103246782
       17 | 113056729
        2 | 120456781
        3 | 123456719
        1 | 133956139
        2 | 143856248
        2 | 153756357
        1 | 163656466
        2 | 173516575
        1 | 183426684
        1 | 193336793

After the main query i'm should have this table:

  counter | forn_primario 
----------+---------------
       17 | 113056729

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-12-02T12:13:08

I think you want this:

Select COUNT(DISTINCT categoria) as contador, forn_primario
From produto
Group by forn_primario
Order by contador DESC
Limit 1;

“column must appear in the GROUP BY clause or be used in an aggregate function”

2 Answers