Group By

I want to retrieve the model of cars in Groups. However the field Model is
filled with the model and the type. Is there a way to group on the first
word, lets say 147, 156, ..
Thx GL
147 1.9 D
147 2.1 D
156 1.6
156 1.7
156 1.9 D
156 2.1 D
156 2.1 D
156 2.5 DHi Gerard,
Try this:
Create Table Model
(
Model varchar(20),
Price Money
)
Insert Into Model (Model, Price)
Values('147 1.9 D',15000)
Insert Into Model (Model, Price)
Values('147 2.1 D',16000)
Insert Into Model (Model, Price)
Values('156 1.6',17000)
Insert Into Model (Model, Price)
Values('156 1.7',18000)
Insert Into Model (Model, Price)
Values('156 1.9 D',20000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.5 D',27000)
Select Left(Model, 3) as 'Model', Avg(Price) as 'AvgPrice'

>From Model
Group By Left(Model, 3)
Drop Table Model
HTH
Barry|||If I'm understanding you...something like this works..
create table #table
(
car_string varchar(1000)
)
insert #table
select '147 1.9 D'
union all
select '147 2.1 D'
union all
select '156 1.6'
union all
select '156 1.7'
select substring(car_string,1,3) car_model,count(*)
from #table
group by substring(car_string,1,3)
order by 1 asc
HTH
MJKulangara
http://sqladventures.blogspot.com|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Why do you have two data elemetns in one column? What the grep()
expression for this violation of 1NF? I woudl guess from this lack of
specs that you want to create a VIEW with the first three characters in
their own column, so you can do a GROUP BY on it.|||In my tables there are fields for
Make
Model
Type
Cc
Carburant
Etc...
But for a unknown reason, maybe laziness, my users fill in all data in
Model.
Of course i can use left(model,3) if every model starts with a 3 charater
group.
So my exemple was wrong. It is not always the first 3 charaters. It is the
part before the first space i like to Group.
Like
Mondeo Gtd
Galaxy 2.0
C220 2.0 D
C220 2.5 tdi
Scenic 2.0
Scenic 2.2
320 TDS
320 TD
So i want the groups Mondeo, Galaxy, C220, Scenic and 320
How can i do this
GL.
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> schreef in bericht
news:jlNEf.232245$XZ3.7544959@.phobos.telenet-ops.be...
>I want to retrieve the model of cars in Groups. However the field Model is
>filled with the model and the type. Is there a way to group on the first
>word, lets say 147, 156, ..
> Thx GL
> 147 1.9 D
> 147 2.1 D
> 156 1.6
> 156 1.7
> 156 1.9 D
> 156 2.1 D
> 156 2.1 D
> 156 2.5 D
>
>|||In that case - try this...
Create Table Model
(
Model varchar(20),
Price Money
)
Insert Into Model (Model, Price)
Values('147 1.9 D',15000)
Insert Into Model (Model, Price)
Values('147 2.1 D',16000)
Insert Into Model (Model, Price)
Values('156 1.6',17000)
Insert Into Model (Model, Price)
Values('156 1.7',18000)
Insert Into Model (Model, Price)
Values('156 1.9 D',20000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.1 D',25000)
Insert Into Model (Model, Price)
Values('156 2.5 D',27000)
Insert Into Model (Model, Price)
Values('Scenic 2.1 D',25000)
Insert Into Model (Model, Price)
Values('Scenic 2.5 D',27000)
Select Left(Model, Charindex(Space(1), Model)) as 'Model', Avg(Price)
as 'AvgPrice'

>From Model
Group By Left(Model, Charindex(Space(1), Model))
Drop Table Model|||IF model and type are supposed to be a limited set of values, meaning there
are valid values which are correct and anythign else is wrong, then you
would want to create a constraint on each of those fields to make sure the
values are valid. This could be done with a foreign key referencing a Model
table and a Type table. You could also make both of these fields required
(not null) and force the users to fill them in.
This will invariably create a stir with your users, but you should be able
to make the argument that having valid (and dependable) data validates the
need for the users changing how they do data entry.
Allowing data entry such as this to persist will only cause more problems
later on, particularly if some of the users are entering the data correctly,
and others are entering it incorrectly.
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:qwYEf.233290$JY3.7518351@.phobos.telenet-ops.be...
> In my tables there are fields for
> Make
> Model
> Type
> Cc
> Carburant
> Etc...
> But for a unknown reason, maybe laziness, my users fill in all data in
> Model.
> Of course i can use left(model,3) if every model starts with a 3 charater
> group.
> So my exemple was wrong. It is not always the first 3 charaters. It is the
> part before the first space i like to Group.
> Like
> Mondeo Gtd
> Galaxy 2.0
> C220 2.0 D
> C220 2.5 tdi
> Scenic 2.0
> Scenic 2.2
> 320 TDS
> 320 TD
> So i want the groups Mondeo, Galaxy, C220, Scenic and 320
> How can i do this
> GL.
> "Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> schreef in bericht
> news:jlNEf.232245$XZ3.7544959@.phobos.telenet-ops.be...
is
>