I need to do a count of a grouped by field. Is this possible?

i.e. Table:

Property Owner
1 Bob
2 Alan
3 Alan
4 Bob
5 Bob
6 Bob

Group By Owner:
Alan
Bob

Count of Group By:
2

How do I write the count of group by in SQL?

"JustinP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> I need to do a count of a grouped by field. Is this possible?
>
> i.e. Table:
>
> Property Owner
> 1 Bob
> 2 Alan
> 3 Alan
> 4 Bob
> 5 Bob
> 6 Bob
>
> Group By Owner:
> Alan
> Bob
>
> Count of Group By:
> 2
>
> How do I write the count of group by in SQL?

As I understand it, you want what in some versions of SQL is available
as a COUNT DISTINCT operation. Access (Jet) SQL doesn't support that,
as far as I know, but you could write this:

SELECT Count(*) FROM
(SELECT DISTINCT Owner FROM Properties);

where "Properties" is the name of the table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Justin

It would be simpler to use a Totals Query.


--
Wayne
Manchester, England.
Enjoy whatever it is you do


"JustinP" wrote:

> I need to do a count of a grouped by field. Is this possible?
>
> i.e. Table:
>
> Property Owner
> 1 Bob
> 2 Alan
> 3 Alan
> 4 Bob
> 5 Bob
> 6 Bob
>
> Group By Owner:
> Alan
> Bob
>
> Count of Group By:
> 2
>
> How do I write the count of group by in SQL?
>
>

I already have other queries within this queries such as:

SELECT Count(*) AS TotalProperties,
Sum(IIf(MasterTable.SECTION_NAME="BCab",1,0)) AS CountOfBundCab,
FROM MasterTable;

Is it possible to include the solution you suggested in that as well?


> As I understand it, you want what in some versions of SQL is available
> as a COUNT DISTINCT operation. Access (Jet) SQL doesn't support that,
> as far as I know, but you could write this:
>
> SELECT Count(*) FROM
> (SELECT DISTINCT Owner FROM Properties);
>
> where "Properties" is the name of the table.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)