Count of a grouped by field

[JustinP]

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?

 

[Dirk Goldgar]

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.

 

[Guest]

Hi Justin

It would be simpler to use a Totals Query.

 

[JustinP]

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?