Why when using MIN function and selecting another column, we require GROUP BY clause? Doesn't MIN return single record?

Question

I have two tables vehicles and dealership_vehicles. The dealership_vehicles table has a price column. The vehicles table has a column dealership_vehicle_id which relates to the dealership_vehicles id column in the dealership_vehicles table.

I wanted to return just the vehicle make of the cheapest car.

Why is it that the following query:

select 
    vehicles.make, 
    MIN(dealership_vehicles.price) 
from 
    vehicles inner join dealership_vehicles 
    on vehicles.dealership_vehicle_id=dealership_vehicles.id;

Returns the error:

column "vehicles.make" must appear in the GROUP BY clause or be used in an aggregate function

Since MIN function returns a single value it is plausible that SQL query can be constructed that will return a single value without needing GROUP BY.

Possible duplicate of [Why do we need GROUP BY with AGGREGATE FUNCTIONS?](https://stackoverflow.com/questions/13998552/why-do-we-need-group-by-with-aggregate-functions) — LPK, Jul 03 '18 at 15:02

score 1 · Answer 1 · answered Jul 03 '18 at 15:00

1

Think of the term "GROUP BY" as "for each." It's saying "Give me the MIN of dealership_vehicles.price for each vehicles.make"

So you will need to change your query to:

select 
    vehicles.make, 
    MIN(dealership_vehicles.price) 
from 
    vehicles inner join dealership_vehicles 
    on vehicles.dealership_vehicle_id=dealership_vehicles.id
Group by vehicles.make;

answered Jul 03 '18 at 15:00

DarthTommy

419
2
10

This actually makes sense. I thought MIN() function returns a single record? Which I thought would go in dealership_vehicles table find the min price in that column and return the vehicle make that has that min price. The query above returns multiple records. – O.MeeKoh Jul 03 '18 at 15:06
@O.MeeKoh: `MIN` returns a single *value*, but that value will be tacked on to each row, so you'll still get a bunch of rows. If you didn't group rows by the other fields, the results would almost never be what you'd want anyway. In your case, it would have been a bunch of rows like `(make, MIN(all the rows))` if it'd worked. – cHao Jul 03 '18 at 15:14

Bob Jarvis - Слава Україні · Accepted Answer · 2018-07-03T21:08:48.073

You say you want to know the make of the cheapest car. The easiest way to do this is

SELECT DISTINCT v.MAKE
  FROM VEHICLE v
  INNER JOIN DEALERSHIP_VEHICLES dv
    ON v.DEALERSHIP_VEHICLE_ID = dv.ID
  WHERE dv.PRICE = (SELECT MIN(PRICE) FROM DEALERSHIP_VEHICLES);

Note that because multiple vehicles might have the "cheapest" price it's entirely possible you'll get multiple returns from the above query.

Best of luck.

EDIT

Another way to do it is to take the minimum price, by make, then sort by the minimum price, and then just take the first row. Something like

SELECT *
  FROM (SELECT v.MAKE, MIN(dv.PRICE)
          FROM VEHICLE v
          INNER JOIN DEALERSHIP_VEHICLES dv
            ON v.DEALERSHIP_VEHICLE_ID = dv.ID
          GROUP BY v.MAKE
          ORDER BY MIN(dv.PRICE) ASC)
  WHERE ROWNUM = 1;

This is EXACTLY what I was looking for! A single query that will return JUST the make of the cheapest vehicle. I just couldnt put it into SQL context. — O.MeeKoh, Jul 03 '18 at 15:36

score 0 · Answer 3 · answered Jul 03 '18 at 15:07

If you want the make of the cheapest car, then no aggregation is needed:

select v.make, dv.price
from vehicles v inner join
     dealership_vehicles  dv
     on v.dealership_vehicle_id = dv.id
order by dv.price asc
fetch first one row only;

This gets a little more complicated if you want all rows in the case of ties:

select v.*
from (select v.make, dv.price, rank() over (order by price asc) as seqnum
      from vehicles v inner join
           dealership_vehicles  dv
           on v.dealership_vehicle_id = dv.id
     ) v
where seqnum = 1

score 0 · Answer 4 · answered Jul 03 '18 at 15:17

So let's say after we join in the price we have the following table (i.e. stored in a #temp table):

#temp Vehicles table:
| Make   | Model |    Price |
|--------|-------|----------|
| Toyota | Yaris |  5000.00 |
| Toyota | Camry | 10000.00 |
| Ford   | Focus |  7500.00 |

If you query it for minimum price without specifying what you're grouping by, then only one minimum function is applied across all of the rows. Example:

select min(Price) from #temp

will return you a single value of 5000.00

If you want to know the make of the cheapest car, you need to filter your results by the cheapest price - it's a two step process. First you find out the cheapest price using min, then in a separate query, you find out which cars are at that price. Once you construct your query correctly, you will notice that this reveals what you might not have though of - you can actually have more than one cheapest make.

Example table:

#temp Vehicles table v2:
| Make   | Model  |    Price |
|--------|--------|----------|
| Toyota | Yaris  |  5000.00 |
| Toyota | Camry  | 10000.00 |
| Ford   | Focus  |  7500.00 |
| Ford   | Escort |  5000.00 |

query:

select * from #temp
where Price = (select min(Price) from #temp)

result:

| Make   | Model  |    Price |
|--------|--------|----------|
| Toyota | Yaris  |  5000.00 |
| Ford   | Escort |  5000.00 |

Why when using MIN function and selecting another column, we require GROUP BY clause? Doesn't MIN return single record?

4 Answers4

EDIT