Help on a Query  
Author Message
thegerrymon





PostPosted: Tue Jun 27 06:55:01 CDT 2006 Top

SQL Server Developer >> 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.

SQL Server95  
 
 
ML





PostPosted: Tue Jun 27 06:55:01 CDT 2006 Top

SQL Server Developer >> Help on a Query 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/
 
 
Wayne





PostPosted: Tue Jun 27 08:53:01 CDT 2006 Top

SQL Server Developer >> Help on a Query I appreciate the response but that results in a row for every value of
Address2. I want the distict count

Wayne



> 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/


 
 
ShaAnand





PostPosted: Tue Jun 27 09:11:02 CDT 2006 Top

SQL Server Developer >> Help on a Query Can you post the DDL for this and some sample data



> 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.
>
>
>
 
 
Wayne





PostPosted: Tue Jun 27 11:51:24 CDT 2006 Top

SQL Server Developer >> Help on a Query 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)


=============================================


> Can you post the DDL for this and some sample data
>

>
>> 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.
>>
>>
>>


 
 
YH





PostPosted: Tue Jun 27 12:53:01 CDT 2006 Top

SQL Server Developer >> Help on a Query try Count(distinct (Area+Address2))?

YH



> 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.
>
>
>
 
 
Wayne





PostPosted: Tue Jun 27 14:13:31 CDT 2006 Top

SQL Server Developer >> Help on a Query I had tried that. I get an error that "distinct" is an undefined function in
the expression?




> try Count(distinct (Area+Address2))?
>
> YH
>

>
>> 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.
>>
>>
>>


 
 
ShaAnand





PostPosted: Wed Jun 28 04:20:02 CDT 2006 Top

SQL Server Developer >> Help on a Query 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



> I had tried that. I get an error that "distinct" is an undefined function in
> the expression?
>
>


> > try Count(distinct (Area+Address2))?
> >
> > YH
> >

> >
> >> 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.
> >>
> >>
> >>
>
>
>
 
 
Wayne





PostPosted: Wed Jun 28 09:49:25 CDT 2006 Top

SQL Server Developer >> Help on a Query Sha;

Interesting approch but when I try it I get a "Syntax error (missing
operator) in expression 'Count(DISTINCT ISNULL(ResidentList.Address2,''))'

Wayne



> 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
>

>
>> I had tried that. I get an error that "distinct" is an undefined function
>> in
>> the expression?
>>
>>


>> > try Count(distinct (Area+Address2))?
>> >
>> > YH
>> >

>> >
>> >> 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.
>> >>
>> >>
>> >>
>>
>>
>>


 
 
ShaAnand





PostPosted: Thu Jun 29 06:04:01 CDT 2006 Top

SQL Server Developer >> Help on a Query 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



> Sha;
>
> Interesting approch but when I try it I get a "Syntax error (missing
> operator) in expression 'Count(DISTINCT ISNULL(ResidentList.Address2,''))'
>
> Wayne
>


> > 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
> >

> >
> >> I had tried that. I get an error that "distinct" is an undefined function
> >> in
> >> the expression?
> >>
> >>


> >> > try Count(distinct (Area+Address2))?
> >> >
> >> > YH
> >> >

> >> >
> >> >> 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
 
 
Wayne





PostPosted: Thu Jun 29 14:51:55 CDT 2006 Top

SQL Server Developer >> Help on a Query Yep - I am using Access 2003. I'll have to find an equivalent.

Wayne



> 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
>

>
>> Sha;
>>
>> Interesting approch but when I try it I get a "Syntax error (missing
>> operator) in expression 'Count(DISTINCT
>> ISNULL(ResidentList.Address2,''))'
>>
>> Wayne
>>


>> > 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
>> >

>> >
>> >> I had tried that. I get an error that "distinct" is an undefined
>> >> function
>> >> in
>> >> the expression?
>> >>
>> >>


>> >> > try Count(distinct (Area+Address2))?
>> >> >
>> >> > YH
>> >> >

>> >> >
>> >> >> 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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


 
 
Stu





PostPosted: Thu Jun 29 14:58:32 CDT 2006 Top

SQL Server Developer >> Help on a Query What's up with all the people posting in the wrong groups lately?

Wayne, you may try replacing the ISNULL in Sha's suggestion with
COALESCE; COALESCE is the ANSI-compatible version, and since SQL Server
and Access both claim to be ANSI-compatible, it may work.

If not, try an Access user group.

HTH,
Stu

> 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
>

>
> > Sha;
> >
> > Interesting approch but when I try it I get a "Syntax error (missing
> > operator) in expression 'Count(DISTINCT ISNULL(ResidentList.Address2,''))'
> >
> > Wayne
> >


> > > 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
> > >

> > >
> > >> I had tried that. I get an error that "distinct" is an undefined function
> > >> in
> > >> the expression?
> > >>
> > >>


> > >> > try Count(distinct (Area+Address2))?
> > >> >
> > >> > YH
> > >> >

> > >> >
> > >> >> 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.
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

 
 
Wayne





PostPosted: Fri Jun 30 05:18:24 CDT 2006 Top

SQL Server Developer >> Help on a Query Sorry about posting that question here instead of in the Access group. Just
not thinking! I did post in an Access group and I'll see what responses I
get there.

Wayne



> What's up with all the people posting in the wrong groups lately?
>
> Wayne, you may try replacing the ISNULL in Sha's suggestion with
> COALESCE; COALESCE is the ANSI-compatible version, and since SQL Server
> and Access both claim to be ANSI-compatible, it may work.
>
> If not, try an Access user group.
>
> HTH,
> Stu

>> 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
>>

>>
>> > Sha;
>> >
>> > Interesting approch but when I try it I get a "Syntax error (missing
>> > operator) in expression 'Count(DISTINCT
>> > ISNULL(ResidentList.Address2,''))'
>> >
>> > Wayne
>> >


>> > > 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
>> > >

>> > >
>> > >> I had tried that. I get an error that "distinct" is an undefined
>> > >> function
>> > >> in
>> > >> the expression?
>> > >>
>> > >>


>> > >> > try Count(distinct (Area+Address2))?
>> > >> >
>> > >> > YH
>> > >> >

>> > >> >
>> > >> >> 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.
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >
>