SELECT DISTINCT or GROUP BY

[Mondeo]

Hi,

 

I have this statement

 

SELECT VehicleID,Make,Model,Term,Price FROM tblVehicles

 

My table looks like this

 

100 AUDI A4 12 199.99

100 AUDI A4 24 299.99

100 AUDI A4 36 399.99

101 AUDI A6 12 199.99

101 AUDI A6 24 299.99

101 AUDI A6 36 399.99

 

Using the statement returns 6 records as expected, but I only want to return each vehicleID once, i've tried SELECT DISTINCT and also GROUP BY but both returned errors.

 

Whats the correct way to do it?

 

Thanks

[amir100]

I'm not sure about what you mean by "VehicleID only appearing once". Can you show me (in the form of table) what is it that you want?

 

I'm just trying to help. :D

[Mondeo]

Thanks Amir,

 

Based on that table, the query I want should return the following

 

100 AUDI A4 12 199.99

101 AUDI A6 12 199.99

 

So each vehicle ID (in this case 100 and 101) are only returned once.

 

Thanks

[amir100]

That would be difficult if you're simply using SELECT DISTINCT. It would be easier if you only needed to show VehicleID, Make, and Model. To do that you only need to use this query:

 

SELECT DISTINCT VehicleID,Make,Model FROM tblVehicles

 

You have to remember that SELECT DISTINCT is use to return the unique rows from performing the typical SELECT.

 

Using the query above without the DISTINCT will return this result.

 

100 AUDI A4

100 AUDI A4

100 AUDI A4

101 AUDI A6

101 AUDI A6

101 AUDI A6

 

The DISTINCT will return this result.

 

100 AUDI A4

101 AUDI A6

 

I hope I'm clear enough with the explanation.

 

The same comes by using GROUP BY. If you're only showing results without using aggregate functions (e.g. SUM, MIN, MAX) then there's no point in using a GROUP BY clause.

 

Maybe you need to add a WHERE clause to your query. For example:

 

SELECT VehicleID,Make,Model,Term,Price FROM tblVehicles

WHERE Term = 12

 

I'm assuming Term's datatype is INTEGER or such.

 

I hope you get my point. :D

 

CMIIW Please. :D

[Mondeo]

Thanks Amir, I think I get it.

 

What about if I used this?

 

SELECT make,model,term, MIN(price) FROM tblVehicles GROUP BY price

 

Would that return

 

100 AUDI A4 12 199.99

101 AUDI A6 12 199.99

 

???

 

Thanks

[amir100]

Thanks Amir, I think I get it.

 

What about if I used this?

 

SELECT make,model,term, MIN(price) FROM tblVehicles GROUP BY price

 

Would that return

 

100 AUDI A4 12 199.99

101 AUDI A6 12 199.99

 

???

 

Thanks

 

Sorry for the late reply Mondeo. I was sick for a couple of weeks.

 

So what you're trying to do is to get the minimum price of each make and model right?

 

In that case then you're halfway right. It's just that you need to fix your GROUP BY expression. Here's a general clue on using aggregate functions with GROUP BY expression.

 

"You have to include all the columns in the SELECT expression which is not used by the aggregate function into the GROUP BY expression."

 

So your query should be like this.

 

SELECT make,model,term,MIN(price) FROM tblVehicles GROUP BY make,model,term

 

Again. CMIIW please. :D