CODEWITHSUNDEEP
sqloracle-databaseselectgroup-bymax
Panagiss
Panagiss

Reputation: 3720

"select list inconsistent with GROUP BY" when using MAX function

I don't get why this one doesn't work :

SELECT stat1, max(stat2)
FROM games
WHERE stat3= x;

But that does:

SELECT max(stat2)
FROM games
WHERE stat3= x;

im new with sql and functions so i don't understand that well what that error wants to indicate or fix.. (I get the error "select list inconsistent with GROUP BY ; amend GROUP BY clause to stat1")

Upvotes: 1

Views: 2216

Answers (3)

GMB
GMB

Reputation: 222512

Every non-aggregated expression in the select clause must appear in the group by clause.

The second query has just one expression in the select clause, which is an aggregation function - so no group by clause is needed. This gives you the maximum value of stat2 for rows where stat3 matches the parameter.

The first query has a non-aggregate expression in the select clause (stat1) and no group by clause, which makes it invalid SQL. If you want the maximum stat2 *per distinct value of stat1, then add a group by clause to the query:

SELECT stat1, max(stat2) FROM games WHERE stat3= x GROUP BY stat1;

On the other hand, if you want to know what is the value of stat1 for which stat2 reaches its max value, then it is different; this is called a greatest-n-per-group problem, and you can solve it by filtering rather than aggregating, as:

select g.*
from games g
where 
    g.stat3 = :x
    and g.stat2 = (select max(g1.stat2) from games g1 where g1.stat3 = :x)

Upvotes: 2

Dave Costa
Dave Costa

Reputation: 48121

SELECT stat1 FROM .. by itself means to return the individual values of stat1 for all matching rows. It will produce one output row for each row selected by the WHERE clause.

SELECT max(stat2) FROM .. by itself means to find the maximum value of stat2 over all matching rows. It will produce a single output row, regardless of how many input rows are processed by the query.

SELECT stat1, max(stat2) FROM .. is apparently inconsistent. The first expression implies that we want many rows. The second expression implies that we want one row.

If you add GROUP BY stat1 to the query, that resolves the inconsistency. It indicates that the input rows should be grouped together by the value of stat1, and that max(stat2) should be calculated independently for each of those groups.

Upvotes: 2

ITHelpGuy
ITHelpGuy

Reputation: 1037

Please research how to use aggregate functions in sql. The first query should have a group by. Tag the appropriate database to further assist you.

SELECT stat1, 
       max(stat2) AS MaxStat2
  FROM games
 WHERE stat3= x
 GROUP BY stat1;

Upvotes: 4

Related Questions