Help on a Query

I have a table (ResidentList) which includes the following fields
- ID (autonumber)
- Address2 (text)
- Area (text)
Address2 usually contains an apartment number like "122 GV" but is always
blank when Area is "Duplex"
Area contains a string like "Glen View", "Northwind", "Duplex", ec.
I want a query to return the count of unique values for the combination of
(Address2 + Area) and the total count of IDs. The result must be grouped by
Area
I get all tangled up trying to code the count of unique values of Address2 +
Area. I think I am approaching it the wrong way. To get just the count of
IDs grouped by Area I use the following query which works fine but when I
start trying to add the unique values of Address2 + Area I can't find the
correct syntax.
SELECT ResidentList.Area, Count(ResidentList.ID) AS CountOfID
FROM ResidentList
GROUP BY ResidentList.Area
ORDER BY Count(ID) DESC;
--
Any suggestions are appreciated.Try this:
SELECT ResidentList.Area, ResidentList.Address2, Count(ResidentList.ID) AS
CountOfID
FROM ResidentList
GROUP BY ResidentList.Area, ResidentList.Address2
ORDER BY Count(ID) DESC;
ML
http://milambda.blogspot.com/|||I appreciate the response but that results in a row for every value of
Address2. I want the distict count
Wayne
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A44A7033-F87F-4C4B-B713-149331B17AA6@.microsoft.com...
> Try this:
> SELECT ResidentList.Area, ResidentList.Address2, Count(ResidentList.ID) AS
> CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area, ResidentList.Address2
> ORDER BY Count(ID) DESC;
>
> ML
> --
> http://milambda.blogspot.com/|||Can you post the DDL for this and some sample data
"Wayne Wengert" wrote:

> I have a table (ResidentList) which includes the following fields
> - ID (autonumber)
> - Address2 (text)
> - Area (text)
> Address2 usually contains an apartment number like "122 GV" but is always
> blank when Area is "Duplex"
> Area contains a string like "Glen View", "Northwind", "Duplex", ec.
> I want a query to return the count of unique values for the combination of
> (Address2 + Area) and the total count of IDs. The result must be grouped b
y
> Area
> I get all tangled up trying to code the count of unique values of Address2
+
> Area. I think I am approaching it the wrong way. To get just the count of
> IDs grouped by Area I use the following query which works fine but when I
> start trying to add the unique values of Address2 + Area I can't find the
> correct syntax.
> --
> SELECT ResidentList.Area, Count(ResidentList.ID) AS CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area
> ORDER BY Count(ID) DESC;
> --
> Any suggestions are appreciated.
>
>|||Sorry - Should have included that in the original post... I needed to
obscure some personal information first.
========================================
=====
CREATE TABLE [ResidentList] (
[ID] [int] NOT NULL ,
[LastName] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [nvarchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(1,
'Burton', 'Betty', '2101 S. Podunk Ave', '139 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-9024', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(2,
'Parker', 'Bonnie', '2101 S. Podunk Ave.', '138 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-6738', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(3,
'Galloway', 'Richard', '2101 S. Podunk Ave.', '126 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-3213', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(4,
'Martin', 'Ruth A.', '2113 S. Podunk Ave.', NULL, 'SomeCity', 'CA', '12345',
'Duplex', '555-1855', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(5,
'Adams', 'Ike', '2101 S. Podunk Ave.', '234 B', 'SomeCity', 'CA', '12345',
'Boulevard', '555-9679', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(6,
'Anderson', 'Barbara', '2101 S. Podunk Ave.', '039 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-1286', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(7,
'Bachman', 'Joe', '2101 S. Podunk Ave.', '252 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-5012', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(8,
'Bailey', 'Richard', '2101 S. Podunk Ave.', '156 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-6454', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(9,
'Baker', 'Jewell', '2101 S. Podunk Ave.', '136 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-37778', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(10,
'Ballard', 'Margie', '2101 S. Podunk Ave.', '043 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-4623', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(11,
'Benson', 'Helen Louise', '2101 S. Podunk Ave.', '409 AL', 'SomeCity', 'CA',
'12345', 'Assisted Living', '555-0684', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(12,
'Bernhardt', 'Irene', '2101 S. Podunk Ave.', '130 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-0771', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(13,
'Bigge', 'CArrine', '2101 S. Podunk Ave.', '121 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-4052', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(14,
'Bischoff', 'Donna', '2101 S. Podunk Ave.', '135 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-3739', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(15,
'Bishop', 'Ruth', '2101 S. Podunk Ave.', '204 AL', 'SomeCity', 'CA',
'12345', 'Assisted Living', '555-9241', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(16,
'Blomberg', 'Ruth', '2101 S. Podunk Ave.', '226 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-9772', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(17,
'Boeke', 'Adah', '2101 S. Podunk Ave.', '427 BS', 'SomeCity', 'CA', '12345',
'Boulevard South', '555-5359', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(18,
'Bower', 'Dawn', '2101 S. Podunk Ave.', '137 GV', 'SomeCity', 'CA', '12345',
'Garden View', '555-2020', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(19,
'Brennesholt', 'Evelyn', '2101 S. Podunk Ave.', '315 AL', 'SomeCity', 'CA',
'12345', 'Assisted Living', '613-1558', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(20,
'Brown', 'Roberta', '2101 S. Podunk Ave.', '038 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-8590', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(21,
'Brownlee', 'Jo', '2101 S. Podunk Ave.', '433 B', 'SomeCity', 'CA', '12345',
'Boulevard', '555-4432', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(22,
'Bruce', 'William', '2101 S. Podunk Ave.', '158 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0161', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(23,
'Bruce', 'Thelma', '2101 S. Podunk Ave.', '158 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0161', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(24,
'Bucklen', 'Helen', '2101 S. Podunk Ave.', '258 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0327', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(25,
'Bucklen', 'Sharon', '2101 S. Podunk Ave.', '258 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0327', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(26,
'Callahan', 'Margaret', '2101 S. Podunk Ave.', '322 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-8198', NULL)
========================================
=====
"Sha Anand" <ShaAnand@.discussions.microsoft.com> wrote in message
news:18A0273D-337D-4E90-916C-52EDA2A0DA3C@.microsoft.com...
> Can you post the DDL for this and some sample data
> "Wayne Wengert" wrote:
>|||try Count(distinct (Area+Address2))?
YH
"Wayne Wengert" wrote:

> I have a table (ResidentList) which includes the following fields
> - ID (autonumber)
> - Address2 (text)
> - Area (text)
> Address2 usually contains an apartment number like "122 GV" but is always
> blank when Area is "Duplex"
> Area contains a string like "Glen View", "Northwind", "Duplex", ec.
> I want a query to return the count of unique values for the combination of
> (Address2 + Area) and the total count of IDs. The result must be grouped b
y
> Area
> I get all tangled up trying to code the count of unique values of Address2
+
> Area. I think I am approaching it the wrong way. To get just the count of
> IDs grouped by Area I use the following query which works fine but when I
> start trying to add the unique values of Address2 + Area I can't find the
> correct syntax.
> --
> SELECT ResidentList.Area, Count(ResidentList.ID) AS CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area
> ORDER BY Count(ID) DESC;
> --
> Any suggestions are appreciated.
>
>|||I had tried that. I get an error that "distinct" is an undefined function in
the expression?
"YH" <YH@.discussions.microsoft.com> wrote in message
news:904262EB-0A7F-4111-99FA-123CF4CB36D3@.microsoft.com...
> try Count(distinct (Area+Address2))?
> YH
> "Wayne Wengert" wrote:
>|||Is this what you are looking at
SELECT ResidentList.Area, Count(DISTINCT ISNULL(ResidentList.Address2,''))
AS CountOfID
FROM ResidentList
GROUP BY ResidentList.Area
ORDER BY ResidentList.Area
Assisted Living 3
Blue Heron 5
Boulevard 6
Boulevard South 1
Duplex 1
Garden View 8
- Sha Anand
"Wayne Wengert" wrote:

> I had tried that. I get an error that "distinct" is an undefined function
in
> the expression?
>
> "YH" <YH@.discussions.microsoft.com> wrote in message
> news:904262EB-0A7F-4111-99FA-123CF4CB36D3@.microsoft.com...
>
>|||Sha;
Interesting approch but when I try it I get a "Syntax error (missing
operator) in expression 'Count(DISTINCT ISNULL(ResidentList.Address2,''))'
Wayne
"Sha Anand" <ShaAnand@.discussions.microsoft.com> wrote in message
news:F235F5F4-BD81-49FA-AFF6-CDD04ED92CC7@.microsoft.com...
> Is this what you are looking at
>
> SELECT ResidentList.Area, Count(DISTINCT ISNULL(ResidentList.Address2,''))
> AS CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area
> ORDER BY ResidentList.Area
> Assisted Living 3
> Blue Heron 5
> Boulevard 6
> Boulevard South 1
> Duplex 1
> Garden View 8
>
> - Sha Anand
> "Wayne Wengert" wrote:
>|||Hi Wayne,
The query i sent you works fine in SQL 2000,
by looking at the error msg - i assume that you are trying to run this
in MS-Access. All SQL 2000 queries may not be compatible with MS-Access.
You need to check MS-Access documentation.
Otherwise you can create a view in SQL 2000 and use it in MS-Access
- Sha Anand
"Wayne Wengert" wrote:

> Sha;
> Interesting approch but when I try it I get a "Syntax error (missing
> operator) in expression 'Count(DISTINCT ISNULL(ResidentList.Address2,''))'
> Wayne
> "Sha Anand" <ShaAnand@.discussions.microsoft.com> wrote in message
> news:F235F5F4-BD81-49FA-AFF6-CDD04ED92CC7@.microsoft.com...
>
>