Grouping query

Bit stumped by this one, any advice would be appreciated.
I have two tables ([owner] and [cars]) which have a one-2-many relationship
(i.e. one owner can own one or multiple cars, a car can have but one owner).
The cars have several properties: number-plate, make, model, colour & fuel,
so for example: A123 456P, BMW, 850, red, petrol, the number-plate making it
unique.
Ignoring, the number plate property, the other 4 fields can be duplicated.
So, there are several owners who own a red BMW 850 petrol.
What I need to do is this.
I need to bring back a list of all the owner IDs and "group" them together
when they have IDENTICAL car COLLECTIONS.
So, imagine that there are four owners who all own only 3 cars: 1 x red BMW
850 petrol, 1 x blue Ford Escort Diesel and 1 x pink VW golf diesel then I'd
want their owner ID's all with a group ID of (say) 6.
234, 6
368, 6
573, 6
962, 6
Similarly for all owners.
Any suggestions?
Many thanks
GriffGriff
Please post DDL+ sample data + expected result
CREATE TABLE Owners
(
OwnerId INT NOT NULL PRIMARY KEY,
...
...
)
CREATE TABLE Cars
(
CarId INT NOT NULL PRIMARY KEY
Ownerid INT NOT NULL ...
)
INSERT INTO Owners VALUES ....
INSERT INTO Cars VALUES ......
I'd like to get the below output
............
"Griff" <Howling@.The.Moon> wrote in message
news:OxORVEQYFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Bit stumped by this one, any advice would be appreciated.
> I have two tables ([owner] and [cars]) which have a one-2-many
relationship
> (i.e. one owner can own one or multiple cars, a car can have but one
owner).
> The cars have several properties: number-plate, make, model, colour &
fuel,
> so for example: A123 456P, BMW, 850, red, petrol, the number-plate making
it
> unique.
> Ignoring, the number plate property, the other 4 fields can be duplicated.
> So, there are several owners who own a red BMW 850 petrol.
> What I need to do is this.
> I need to bring back a list of all the owner IDs and "group" them together
> when they have IDENTICAL car COLLECTIONS.
> So, imagine that there are four owners who all own only 3 cars: 1 x red
BMW
> 850 petrol, 1 x blue Ford Escort Diesel and 1 x pink VW golf diesel then
I'd
> want their owner ID's all with a group ID of (say) 6.
> 234, 6
> 368, 6
> 573, 6
> 962, 6
> Similarly for all owners.
> Any suggestions?
> Many thanks
> Griff
>|||Here goes:
SQL for creation is as follows:
========================================
===========================
CREATE TABLE [dbo].[owners] (
[ownerID] [int] IDENTITY (1, 1) NOT NULL ,
[surname] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owners] ADD
CONSTRAINT [PK_owners] PRIMARY KEY CLUSTERED
(
[ownerID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cars] (
[carID] [int] IDENTITY (1, 1) NOT NULL ,
[ownerID] [int] NOT NULL ,
[registration] [char] (8) COLLATE Latin1_General_CI_AS NOT NULL ,
[make] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[model] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[colour] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cars] ADD
CONSTRAINT [PK_cars] PRIMARY KEY CLUSTERED
(
[carID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cars] ADD
CONSTRAINT [FK_cars_owners] FOREIGN KEY
(
[ownerID]
) REFERENCES [dbo].[owners] (
[ownerID]
)
insert into owners (surname) values ('smith')
insert into owners (surname) values ('davey')
insert into owners (surname) values ('bird')
insert into owners (surname) values ('gates')
insert into cars (ownerid, registration, make, model, colour) values
(1,'abcdefgh','bmw','850','red')
insert into cars (ownerid, registration, make, model, colour) values
(2,'bcdefghi','fiat','panda','blue')
insert into cars (ownerid, registration, make, model, colour) values
(2,'cdefghij','bmw','850','red')
insert into cars (ownerid, registration, make, model, colour) values
(3,'defghijk','bmw','850','red')
insert into cars (ownerid, registration, make, model, colour) values
(4,'efghijkl','fiat','panda','blue')
insert into cars (ownerid, registration, make, model, colour) values
(4,'fghijklm','bmw','850','red')
========================================
===========================
Results wanted:
A car is considered identical to another car if the MAKE, MODEL and COLOUR
are identical (not ID or registration)
I want to create an arbitary grouping "letter" to group all owner IDs that
own the same collection of cars
Owner ID Group Code
1 A
2 B
3 A
4 B
Both owners 1 & 3 both own one car and that car is a red BMW 850 - they
therefore get assigned group code A (could be a group ID 1, doesn't matter)
Both owners 2 & 4 own two cars, one a red BMW 850 and a blue Fiat Panda, so
are assigned a different group code.
It's really saying "I want to bracket together all the people who have an
identical set of cars in their garage"
Hope this helps!
Griff
========================================
===========================|||Griff
SELECT O.ownerid,COUNT(c.ownerid)AS GroupId FROM Owners
o JOIN Cars c ON o.ownerid=c.ownerid
GROUP BY O.ownerid
"Griff" <Howling@.The.Moon> wrote in message
news:%23Ie7CTRYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Here goes:
> SQL for creation is as follows:
> ========================================
===========================
> CREATE TABLE [dbo].[owners] (
> [ownerID] [int] IDENTITY (1, 1) NOT NULL ,
> [surname] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[owners] ADD
> CONSTRAINT [PK_owners] PRIMARY KEY CLUSTERED
> (
> [ownerID]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cars] (
> [carID] [int] IDENTITY (1, 1) NOT NULL ,
> [ownerID] [int] NOT NULL ,
> [registration] [char] (8) COLLATE Latin1_General_CI_AS NOT NULL ,
> [make] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [model] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [colour] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[cars] ADD
> CONSTRAINT [PK_cars] PRIMARY KEY CLUSTERED
> (
> [carID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[cars] ADD
> CONSTRAINT [FK_cars_owners] FOREIGN KEY
> (
> [ownerID]
> ) REFERENCES [dbo].[owners] (
> [ownerID]
> )
> insert into owners (surname) values ('smith')
> insert into owners (surname) values ('davey')
> insert into owners (surname) values ('bird')
> insert into owners (surname) values ('gates')
> insert into cars (ownerid, registration, make, model, colour) values
> (1,'abcdefgh','bmw','850','red')
> insert into cars (ownerid, registration, make, model, colour) values
> (2,'bcdefghi','fiat','panda','blue')
> insert into cars (ownerid, registration, make, model, colour) values
> (2,'cdefghij','bmw','850','red')
> insert into cars (ownerid, registration, make, model, colour) values
> (3,'defghijk','bmw','850','red')
> insert into cars (ownerid, registration, make, model, colour) values
> (4,'efghijkl','fiat','panda','blue')
> insert into cars (ownerid, registration, make, model, colour) values
> (4,'fghijklm','bmw','850','red')
> ========================================
===========================
> Results wanted:
> A car is considered identical to another car if the MAKE, MODEL and COLOUR
> are identical (not ID or registration)
> I want to create an arbitary grouping "letter" to group all owner IDs that
> own the same collection of cars
> Owner ID Group Code
> 1 A
> 2 B
> 3 A
> 4 B
> Both owners 1 & 3 both own one car and that car is a red BMW 850 - they
> therefore get assigned group code A (could be a group ID 1, doesn't
matter)
> Both owners 2 & 4 own two cars, one a red BMW 850 and a blue Fiat Panda,
so
> are assigned a different group code.
> It's really saying "I want to bracket together all the people who have an
> identical set of cars in their garage"
> Hope this helps!
> Griff
> ========================================
===========================
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236uDRRSYFHA.2128@.TK2MSFTNGP14.phx.gbl...

> SELECT O.ownerid,COUNT(c.ownerid)AS GroupId FROM Owners
> o JOIN Cars c ON o.ownerid=c.ownerid
> GROUP BY O.ownerid
Hi Uri
This does not take into account whether the rows actually have the same
values in them.
In the CARS table, change the model from "BMW" to "Bently" and owner 1 will
still end up in the same group as owner 3.
I'd need them to be different - the collection size is the same, but it's a
different collection.
Griff|||Solved it, so thanks everyone!
Griff

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
>