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