using MIN on a datepart with Group BY not working, returns different dates

Question

Hi there,

Can anyone help with an aggregate function.. MIN.

I have a car table that i want to return minimum sale price and minimum year on a tbale that has identical cars but different years and price ...

Basically if i removed Registration (contains a YEAR) from the group by and select the query works but if i leave it in then i get 3 cars returned which are exactly the same model,make etc but with different years..

But i am using MIN so it should return 1 car with the year 2006 (the minimum year between the 3 cars)

The MIN(SalePrice) is working perfectly .. its the registraton thats not owrking..

Any ideas?

SELECT MIN(datepart(year,[Registration])) AS YearRegistered, MIN(SalePrice), Model, Make FROM [VehicleSales] GROUP BY datepart(year,[Registration]), Model, Make

Answer 1

You're getting what you're asking for: the cars are put into different groups whenever their model, make, or year is different, and the (minimum, i.e. only) year and minimum price for each of those groups is returned.

Why are you using GROUP BY?

Answer 2

IF I have correctly understood what you are looking for, you should query:

SELECT Model, Make, MIN(datepart(year,[Registration])) AS YearRegistered, MIN(SalePrice)
FROM [VehicleSales]
GROUP BY Model, Make

Hope it helps.

Answer 3

Turro answer will return the lowest registration year and the lowest price for (Model, Make), but this doesn't mean that lowest price will be for the car with lowest Year. Is it what you need?

Or, you need one of those:

1. lowest price between the cars having lowest year

2. lowest year between the cars having lowest price

-- EDITED ---

You are correct about the query, but I want to find the car make/model that gets cheaper the next year ;)

That's why I made a comment. Imagine next situation

Porshe 911 2004 2000
Porshe 911 2004 3000
Porshe 911 2005 1000
Porshe 911 2005 5000

You'll get result that will not really tell you if this car goes cheaper based on year or not.

Porshe 911 2004 1000

I don't know how you'll tell if car gets cheaper next year based on one row without comparison with previous year, at least.

P.S. I'd like to buy one of cars above for listed price :D

Answer 4

You are correct about the query, but I want to find the car make/model that gets cheaper the next year ;)

You should find cheapest (or average) make/model per year and compare with the cheapest (or average) from previous year (for the same make/model).

Then you can see which of them gets cheaper the next year (I suppose most of them)