CODEWITHSUNDEEP
sqlpostgresql
Denys
Denys

Reputation: 4557

Problems with distinct in SQL query

Okay, i've been trying it for a while and haven't succeeded yet, it's kind of mystical, so please help.

vehicle table

Here is my table. I need to select all distinct models and group/order them by the vehicle_type. Everything is ok until I start using DISTINCT. I'm using postgres Little help with query please?

Upvotes: 0

Views: 127

Answers (2)

Gavin
Gavin

Reputation: 6470

The data model does not adequately capture your reporting requirments as the column data needs to be inspected to categorise it but something like: (Extrapolating a possible relationship from your description)

SELECT CASE (vt.description ~ 'car$') 
       WHEN TRUE THEN 'car'
                 ELSE 'van' 
       END AS vehicle_group,
       vt.description AS vehicle_sub_group,
       COUNT (*) -- or whatever aggregates you might need
  FROM vehicle v
       INNER JOIN vehicle_type vt ON vt.vehicle_type = v.vehicle_type
 GROUP BY 1,2;

Might get you towards what you need in the stated case, however it is a fragile way of dealing with data and will not cope well with additional complexities e.g. if you need to further split car into saloon car, sports car, 4WD or van into flatbed, 7.5 ton, 15 ton etc.

Upvotes: 1

Rudu
Rudu

Reputation: 15892

Assuming model could be shared between several vehicle types:

SELECT vehicle_type,model 
FROM vehicle 
GROUP BY vehicle_type,model 
ORDER BY vehicle_type,model

Upvotes: 2

Related Questions