hey all,
i have 2 tables. i'm grouping on table1 i'd like to join this to table2
which would create 1 to 1 relationship. when add extra fields from table2 it
forces me to group by these fields as well. Can someone please explain this
concept to me?
thanks,
rodcharCould you give better specs?
http://www.aspfaq.com/5006
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:DAD412E6-9F72-42D2-8220-A8905E863944@.microsoft.com...
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
> it
> forces me to group by these fields as well. Can someone please explain
> this
> concept to me?
> thanks,
> rodchar|||Can you post what you are doing to have an idea of what you are talking abou
t?
AMB
"rodchar" wrote:
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
it
> forces me to group by these fields as well. Can someone please explain thi
s
> concept to me?
> thanks,
> rodchar|||rodchar wrote:
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to
> table2 which would create 1 to 1 relationship. when add extra fields
> from table2 it forces me to group by these fields as well. Can
> someone please explain this concept to me?
> thanks,
> rodchar
All columns must appear in a group by clause unless you are using an
aggregate. From BOL: "When GROUP BY is specified, either each column in
any non-aggregate expression in the select list should be included in
the GROUP BY list, or the GROUP BY expression must match exactly the
select list expression."
If you were allowed to leave a column off the Group By clause, what
value would SQL Server use for the result set (assuming there were
multiple matches)?
You may be able to use a derived table to do what you want, but as Aaron
mentioned, we need some more details.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The GROUP BY clause is explained here:
http://msdn.microsoft.com/library/d...r />
_9sfo.asp
ML|||thanks David and everyone this helped.
"David Gugick" wrote:
> rodchar wrote:
> All columns must appear in a group by clause unless you are using an
> aggregate. From BOL: "When GROUP BY is specified, either each column in
> any non-aggregate expression in the select list should be included in
> the GROUP BY list, or the GROUP BY expression must match exactly the
> select list expression."
> If you were allowed to leave a column off the Group By clause, what
> value would SQL Server use for the result set (assuming there were
> multiple matches)?
> You may be able to use a derived table to do what you want, but as Aaron
> mentioned, we need some more details.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Showing posts with label relationship. Show all posts
Showing posts with label relationship. Show all posts
Friday, March 30, 2012
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
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
Subscribe to:
Posts (Atom)