expression not in aggregate or GROUP BY columns

Question

I have a table that contains the following columns: ProductID, ProductName, Price, CategoryID.

The goal is to print all the details (ProductID, ProductName, Price, and Category) about the most expensive product.

I executed the following query:

str = "SELECT ProductID, ProductName, MAX(Price) AS MaxPrice, CategoryID FROM Products;";   

However, the exception mentioned in the title appears, and I do not find a reason for the exception appearance. I searched for explanations on the following links:

• Expression not in aggregate or GROUP BY columns Exception

• Aggregate columns not in group by

I have also read and practiced in SQL Lesson 10: Queries with aggregates (Pt. 1) According to my checking in SQLBolt, the way I wrote the query is acceptable.

After reading and practicing, I still do not understand why I need to use the GROUP BY here. It's not that I need to present the maximum price of each group...

Thank you very much beforehand!

Answer 1

What you need is only an ORDER BY clause like:

SELECT ProductID, ProductName, Price AS MaxPrice, CategoryID 
FROM Products
ORDER BY Price DESC 
LIMIT 1

LIMIT 1 will make sure that you fetch only the first row.

You got the error because when aggregating (i.e. sum, avg, max, min, count) a numeric column you need to tell SQL how you want to group the data by using the GROUP BY clause. For example if you wanted to find the Maximum price for each ProductName you'll have to use GROUP BY on ProductName:

SELECT ProductName, MAX(Price) AS MaxPrice FROM Products GROUP BY ProductName

Group by (SQL)

why is GROUP BY needed?

This is easier to understand with sum() than max() because for SQL both are aggregation functions. So let's say if you want to get the sum total of Price instead of maximum of price, so you would do:

SELECT SUM(Price) AS Total FROM Products --will give the total of all the prices

but if you added non-aggregated columns:

SELECT ProductName, SUM(Price) AS Total FROM Products --will not work

you will immediately see that this is not possible because there will be multiple product names, which one should be displayed for the sum of prices?. That's why it throws the error asking to use a group by clause.

hope this helps!