Counting number of groups in a query 1

[Terpsfan]

What I am trying to accomplish is the arrive at a count of the total number of groups within a query, instead of the total number of records. I'm able to do this with 2 queries but would like to be able to do it within one. Do I use a subquery for this? If you could provide an example that would help. Thanks...

 

[PHV]

Perhaps you could post your 2 queries SQL code, actual ouput and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Terpsfan]

The table is something like:

ProjectID (autonumber)
ProjectNum (text)
ProjectPhase (integer)

Let's say the data looks like this:

1- AccessTestDB - 1
2- AccessTestDB - 2
3- VBExport -1


I want to count the second column ProjectNum, not the total records. Below groups the projects and gives me a count per project.

qryProjectCount
SELECT ProjectNum, COUNT(ProjectNum) As ProjectCount
FROM tblProjects
GROUP BY ProjectNum;

So then I am having to write another query to count the groups.

SELECT Count(*) As ProjectCount
FROM qryProjectCount;

I would like to do all this in one query. I'm thinking it would involve a subquery but not sure. I haven't seen any examples of this but I am sure it is possible.

 

[PHV]

Something like this ?
SELECT Count(*) As ProjectCount FROM (SELECT DISTINCT ProjectNum FROM tblProjects)
JetSQL seems to choke on COUNT(DISTINCT ...) aggregate function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Terpsfan]

PHV,

That was the answer I was looking for. Thanks a lot.